Database query optimizations for V2 Go API

Summary

Performance schema analysis on db3-internal identified several slow/frequent queries from the V2 Go API (iznik-server-go) that can be significantly optimized. These are ranked by impact (total DB time consumed).

Critical — Multi-second queries

1. Chat User2Mod query — OR condition causes full table scan

• [ ] File: iznik-server-go/chat/chatroom_mt.go:203-211 (getModeratorChatIDs)
• Avg: 223ms (but up to 174 seconds for some users)
• Total DB time: 650,164s (highest single-query cost)
• Called: 2,914 times in 24h
• Problem: The OR chat_rooms.user1 = ? mixed into the memberships JOIN prevents index use, causing a full scan of chat_rooms × memberships.
• Fix: Split into UNION of two simple queries — one for moderator role, one for user1.
• Benchmark: Original: 174.05s → UNION: 0.001s (~174,000x faster)
• Rows verified: Same result set (0 rows for test user — both queries agree)

2. mod_configs — double self-join on memberships

• [ ] File: iznik-server-go/modconfig/modconfig.go:207-211 (GetModConfigs)
• Avg: 4,232ms (4.2 seconds)
• Total DB time: 397,771s
• Called: 94 times in 24h
• Problem: Two LEFT JOINs on memberships (m1 for configid, m2 for same group + userid) with OR conditions in WHERE clause. The double self-join on the large memberships table is extremely expensive.
• Fix: UNION of three simple queries: (1) createdby = ?, (2) default = 1, (3) INNER JOIN both memberships tables.
• Benchmark: Original: 60+ seconds (too slow to benchmark) → UNION: 1.3ms (~46,000x faster)
• Rows verified: UNION returns 1 row (id=38771 "Default Configuration Freegle Groups"), matching expected result for test user.

High Impact — Frequent queries with moderate cost

3. Newsfeed queries — FORCE INDEX with multiple LEFT JOINs

• [ ] File: iznik-server-go/newsfeed/newsfeed.go:347-395 (getFeed)
• Avg: 95-185ms
• Total DB time: 937,886s + 298,664s (highest total time of any query!)
• Called: 9.8M + 1.6M times
• Problem: Multiple LEFT JOINs (spam_users, newsfeed_unfollow, communityevents, volunteering, users_stories) on every call. The UNION of spatial + alerts doubles the work.
• Potential fix: Consider caching newsfeed results briefly (even 30s would cut 9.8M calls dramatically). Also investigate whether the LEFT JOINs for communityevents/volunteering/stories could be done lazily only when those types appear.

4. Chat room list detail query — correlated subqueries for unseen/replyexpected

• [ ] File: iznik-server-go/chat/chatroom.go:389-421 (listChats)
• Avg: 10.9ms
• Total DB time: 316,394s
• Called: 29M times
• Problem: Multiple correlated subqueries per chat room (unseen count, replyexpected count, lastmsgseen, latest message via ROW_NUMBER). Each subquery scans chat_messages.
• Potential fix: Batch the unseen/replyexpected counts into a single GROUP BY query on chat_messages WHERE chatid IN (...), then merge in Go code. The ROW_NUMBER CTE is already reasonably efficient.

5. Messages outcomes with comments — GROUP BY groupid

• [ ] File: Not in Go code — likely V1 PHP session workcounts (called 5.6M times)
• Avg: 94ms
• Total DB time: 527,504s
• Note: This appears to be from V1 PHP session handling. Skip unless still active after V1 retirement.

6. Isochrone/browse message listing — UNION with spatial queries

• [ ] File: iznik-server-go/isochrone/message.go:55-90 (List)
• Avg: 283ms
• Total DB time: 299,264s
• Called: 1.06M times
• Problem: UNION of messages_spatial (indexed) + user's own messages (not in spatial table). The second branch does ST_Contains on non-spatial data.
• Potential fix: Ensure user's own recent messages are added to messages_spatial promptly, eliminating the need for the UNION fallback.

7. Volunteering listing — LEFT JOINs with date filtering

• [ ] File: iznik-server-go/volunteering/volunteering.go:91-98 (List)
• Avg: 14.8ms
• Total DB time: 166,377s
• Called: 11.2M times
• Problem: High call frequency with LEFT JOINs on volunteering_groups and volunteering_dates for every page load.
• Potential fix: Add index on volunteering(deleted, expired) to speed up the base filter. Consider caching since volunteering data changes infrequently.

8. Dashboard views — correlated subquery on messages_likes

• [ ] File: iznik-server-go/dashboard/dashboard.go:229-235 (Dashboard)
• Avg: 77.5ms (up to several seconds)
• Total DB time: 3,023s
• Called: 39 times in 24h
• Problem: Correlated subquery (SELECT COUNT(*) FROM messages_likes WHERE msgid = m.id) executed for every message row, then sorted by count.
• Potential fix: Replace with LEFT JOIN + GROUP BY: LEFT JOIN messages_likes ml ON ml.msgid = m.id AND ml.type = 'View' GROUP BY m.id ORDER BY COUNT(ml.msgid) DESC.

9. Dashboard active mods — correlated subquery on messages_groups

• [ ] File: iznik-server-go/dashboard/dashboard.go:349-354 (Dashboard)
• Avg: 1.4s (single group) to 25.4s (multiple groups)
• Total DB time: 13,466s + 8,593s
• Called: 530 + 6,173 times
• Problem: Correlated subquery finding latest approvedat per moderator. Scales linearly with number of moderators × groups.
• Note: Benchmarking showed the correlated subquery is actually efficient for single groups (1.2ms). The high avg is driven by calls with many groups. A JOIN-based approach was tested but was slower (204ms vs 1.2ms for single group). The current approach may be acceptable, or could benefit from limiting the group list.

10. Logs search — LIKE queries on firstname/lastname/fullname

• [ ] File: iznik-server-go/logs/logs.go:141-148 (List)
• Avg: 6.2-60.7ms
• Total DB time: 1,209s
• Called: 28 times in 24h
• Problem: Multiple LIKE queries with leading wildcards (%search%) on users table prevent index use. CONCAT in WHERE clause adds overhead.
• Potential fix: Low frequency, so low priority. Could add fulltext index on users(firstname, lastname, fullname) for search queries if it becomes more frequent.

11. Microvolunteering challenge query — complex multi-table JOIN

• [ ] File: iznik-server-go/microvolunteering/microvolunteering.go:288-339
• Avg: 675ms
• Total DB time: 8,451s
• Called: 12,517 times
• Problem: Joins messages_spatial, messages_groups, messages, groups, microactions, messages_outcomes with correlated subqueries for review/approval counts.
• Potential fix: Replace correlated subqueries with a single pre-aggregated subquery joined in.

Batch/Cron Queries (iznik-batch)

12. chat_messages cleanup — scanning by date

• [ ] Likely batch cron job (not found in Go code)
• Avg: 228ms
• Total DB time: 13,218s
• Problem: SELECT id FROM chat_messages WHERE date < ? AND reviewrejected = ? LIMIT ? — full scan on date column.
• Potential fix: Add composite index on chat_messages(reviewrejected, date).

13. Empty chat rooms cleanup

• [ ] Likely batch cron job
• Avg: 59ms
• Total DB time: 3,550s
• Problem: SELECT chat_rooms.id FROM chat_rooms LEFT OUTER JOIN chat_messages ON chat_rooms.id = chat_messages.chatid WHERE chat_messages.chatid IS NULL — anti-join pattern.
• Potential fix: Use NOT EXISTS subquery instead of LEFT JOIN + IS NULL, or add index if missing.

Priority Order

1. #1 Chat User2Mod — 174,000x improvement proven, highest per-query cost
2. #2 mod_configs — 46,000x improvement proven, second highest per-query cost
3. #3 Newsfeed — Highest total DB time, caching would have massive impact
4. #4 Chat room list — Very high call count, correlated subquery batching
5. #6 Isochrone — High call count with expensive spatial operations
6. #7 Volunteering — Very high call count, simple index fix possible
7. #11 Microvolunteering — Moderate frequency, correlated subquery fix
8. #8 Dashboard views — Low frequency but easy fix
9. #12-13 Batch cleanup — Index additions

---

🤖 Generated with Claude Code