SQL Indexing for Slow Queries: The Practical Guide (With EXPLAIN Examples)

The Mental Model That Makes Indexes Click

Without an index, a query reads every row (a full table scan). An index is a sorted copy of a few columns with pointers to the real rows — like a book's index. Lookups drop from O(n) to O(log n): a 10-million-row table takes milliseconds with an index and seconds without. The catch: indexes are extra storage and slow down writes (every INSERT/UPDATE maintains them), so you index for the queries that matter, not all columns.

Reading EXPLAIN: The 30-Second Check

Prefix your query with EXPLAIN (MySQL/PG: EXPLAIN SELECT ...; SQLite: EXPLAIN QUERY PLAN). The rows you care about: type (MySQL) or scan type (PG) — you want index/range scans, not ALL/Seq Scan; key (which index was used — NULL means none); rows (estimated rows examined — compare to actual table size). If rows ≈ table size, the index isn't being used and you know to fix the query or the index.

The Three Index Designs That Fix Most Queries

1. Single-column for exact matches: CREATE INDEX idx_email ON users(email); — fixes WHERE email = '[email protected]'. Add UNIQUE if the column is unique; it doubles as a constraint.

2. Composite (multi-column) for combined filters: CREATE INDEX idx_order_customer_date ON orders(customer_id, order_date); — fixes WHERE customer_id = 42 AND order_date BETWEEN .... Column order matters: put the equality column first, the range column last — the index is only used left-to-right, so idx(customer_id, order_date) serves the query above, while idx(order_date, customer_id) wouldn't.

3. Covering index for hot queries: CREATE INDEX idx_orders_covering ON orders(customer_id, total) INCLUDE (status); — when the query only needs indexed columns, the engine never touches the table (an index-only scan). This is the #1 trick for queries that run thousands of times a second.

When Indexes Make Things Worse

• Low-cardinality columns (status, gender, boolean): an index on 3 distinct values rarely helps and adds write overhead.
• Heavy-write tables: every index multiplies INSERT/UPDATE cost. A table with 6 indexes writes 6x the index data per row.
• LIKE patterns with leading wildcards: LIKE '%abc' can't use an index (the value isn't known until the scan). LIKE 'abc%' can.
• Functions on the column: WHERE DATE(created_at) = '2026-01-01' disables the index — use created_at >= '2026-01-01' AND created_at < '2026-01-02', or a functional index.

The Slow-Query Debug Checklist

1. Get the actual query from the slow query log (don't guess which query is slow). 2. Run EXPLAIN and note scan type + rows examined. 3. Fix the query shape first (avoid functions on columns, leading wildcards, and OR across different columns). 4. Add the index the EXPLAIN says is missing. 5. Re-check EXPLAIN — rows examined should drop by 10-100x. 6. Watch for regressions on write-heavy paths. Most "mystery" slow queries are one of: a missing index, an OR that defeats indexes, or a function-wrapped column.

Try It Yourself

Paste your slow query into our free SQL generator and ask "why is this slow and what index do I need" — it'll suggest both the rewritten query and the CREATE INDEX statement for your dialect.