Description
SQL N+1 detection, composite/covering indexes, query rewrite, prioritized by user impact.
Most DB performance problems are too many queries, not slow queries. The N+1 problem alone causes more incidents than all slow-query optimization combined. STEP 1 — N+1 DETECTION Scan for loops containing DB queries: collect IDs then query N times instead of 1 JOIN. Flag each with file:line, expected N from monitoring, cost at N=100 and N=1000. STEP 2 — COMPOSITE INDEX PROPOSAL For each seq scan (from EXPLAIN ANALYZE): ```sql CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC) WHERE status IN ('pending','processing'); -- partial index ``` Composite covering WHERE + ORDER BY beats separate indexes (MySQL uses one index per query). STEP 3 — COVERING INDEX ```sql CREATE INDEX idx_users_active_created_covering ON users (active, created_at) INCLUDE (email, name, avatar_url); ``` INCLUDE columns only at leaf level (no sort overhead), enabling index-only scans. STEP 4 — QUERY REWRITE - CTE (WITH clause): break complex queries into readable steps. - Window function (ROW_NUMBER): replace self-join for deduplication. - Lateral join: call set-returning function per row without N+1. - Batch loading: collect IDs → IN(...) — turns N queries into 1. STEP 5 — PRIORITIZED PLAN (order by user-facing impact, not query duration): | Query | Pattern | Fix | Improvement | | user_posts | N+1 in loop | JOIN eager load | 1000× at N=500 | | active_orders | Seq scan 42k rows | Composite index | ~200× | | recent_signups | Self-join | Window function | ~5× | OUTPUT: Prioritized optimization plan ranked by user-facing latency impact.
No comments yet. Be the first!