Slow Query Optimization

Hook: A slow query is usually the database saying, ‘I had to check too much stuff.’ Interviewers love this topic because it shows whether you can move from guessing to measuring.

Question: What is your approach to slow query optimization in SQL?

Answer: I first reproduce the slow query with real parameters, then inspect the execution plan to see where the database spends time: scanning too many rows, sorting, joining badly, or reading from disk. After that, I usually fix the query shape so it is sargable (search-argument-able, meaning the condition can use an index efficiently), and I add or adjust indexes only where they match the most selective filters and sorts.

Interview-Ready Answer: I optimize slow SQL by measuring first, not guessing. I run the query with EXPLAIN or EXPLAIN ANALYZE, look for full table scans, row estimate mismatches, expensive sorts, and bad join order, then I rewrite the predicate to be index-friendly and add the smallest useful index. For example, I avoid wrapping an indexed column in a function and I prefer a composite index that matches my WHERE and ORDER BY pattern, because that usually cuts work from scanning thousands of rows to touching only a few.

What slow query optimization really means

Optimization is not ‘make every query use an index.’ The goal is to make the database do the least work for the right result. A query can be slow because it reads too many rows, sorts a huge intermediate set, joins tables in the wrong order, or repeatedly hits disk instead of memory.

How it works under the hood

1. Reproduce the problem. Run the exact SQL with realistic values, because a query that is fast for one customer can be slow for another due to data skew, which means the data is unevenly distributed.
2. Read the plan. Use EXPLAIN or EXPLAIN ANALYZE to see whether the optimizer chose a sequential scan, index scan, nested loop, hash join, or sort. A sequential scan means reading the table row by row; that is fine for tiny tables, but painful on large ones.
3. Check row estimates. If the plan expects 10 rows but gets 100,000, the statistics are stale or the predicate is misleading. Bad estimates often cause bad join choices.
4. Fix the query shape. Keep filters simple and index-friendly. For example, created_at >= ... AND created_at < ... is usually better than DATE(created_at) = ... because the latter wraps the column in a function and can block index use.
5. Add the right index. Put equality columns first, then range columns, then columns used for sorting. A composite index is an index on multiple columns; it is useful when one query pattern repeats often.
6. Verify with the same workload. Re-run the query after ANALYZE or after refreshing statistics. If the change helps one query but hurts inserts, updates, or deletes too much, roll it back.

When and why to use indexes

Indexes are great when you filter a small part of a large table, join on keys, or need rows in a specific order. They are less helpful when you return a big fraction of the table, because then a sequential scan can be cheaper than jumping through the index and then back to the table many times.

Performance notes that interviewers like

• Big-O intuition: a table scan is roughly O(n), while index lookup is closer to O(log n) plus the cost of fetching matching rows. That is why indexes shine on large, selective queries.
• Write overhead: every extra index slows INSERT, UPDATE, and DELETE because the index must also be maintained. In real systems, too many indexes can add noticeable latency to writes.
• Selectivity: if a condition matches 30% to 50% of a table, the optimizer may choose a scan anyway, and that can be correct.
• Statistics: stale stats can make the optimizer choose a bad plan even when the right index exists, so refreshing stats matters after large data changes.

Important edge cases

• Functions on columns often break index use: LOWER(email), DATE(created_at), and CAST(id AS TEXT) are common traps.
• LIKE '%abc' usually cannot use a normal B-tree index because the wildcard is at the front.
• NULL is not equal to anything, so = NULL does not work; use IS NULL.
• One index is not always enough. A query that filters by customer_id and sorts by created_at DESC often wants a composite index on both columns in that order.

Memory Hook: Think of a library: a table scan is opening every book, while an index is the catalog card that points you to the exact shelf.

Real-world story

Imagine a checkout service in an e-commerce app. The page that shows a user’s recent orders suddenly jumps from 80 ms to 4 seconds after traffic grows. The logs show many repeated calls to the same query, and the database metrics show high disk reads and ‘rows examined’ far above the number of rows returned.

What went wrong? The developer wrote the date filter as DATE(created_at) = DATE '2024-06-02'. That looked readable, but it forced the database to compute a function on every row, so the index on created_at could not be used. Users saw spinners, retries, and sometimes duplicate checkout clicks because the page felt broken even though the database was still ‘working’ hard in the background.

The fix was to rewrite the filter to a range and add a composite index for the common access pattern. After that, the query stopped reading the whole table and returned fast again.

Follow-up & Tricky Questions

• How do you choose index column order? Put the most selective equality filters first, then range filters, then columns used for sorting. That order helps the database narrow the search early and can avoid an extra sort.
• What is the difference between a composite index and a covering index? A composite index has multiple columns; a covering index also contains every column needed by the query, so the database may not need to hit the table at all.
• How do you read an execution plan? Start with the highest-cost node, then check whether the plan says sequential scan, index scan, sort, or join. Compare estimated rows to actual rows; a big mismatch is a strong clue that statistics or predicates are off.
• When does an index hurt performance? On small tables, very low-selectivity filters, or write-heavy workloads, an index can be net negative because it adds maintenance cost without saving much read work.
• Should you always optimize the slowest query first? Usually yes, but only after confirming it is on the critical path. A query that runs once an hour is less important than a slightly slower query that runs on every page load.
• Why does LIKE '%abc' often not use an index? Because the leading wildcard means the database cannot jump to a predictable starting point in the index; it has to inspect many candidates.
• Can an index on created_at help DATE(created_at)? Not usually, because the function changes the indexed value before comparison. You need to rewrite the predicate or use an expression index if your database supports it and the pattern is common.
• Is SELECT * always bad? Not always, but it often increases I/O and memory use. Selecting only the needed columns makes index-only or covered plans more likely and reduces network cost too.

Common gotchas people miss

• Do not guess first. The fix is often not the first thing you think of; the plan tells you whether the bottleneck is scan, sort, join, or I/O.
• Do not index everything. Extra indexes slow writes and waste storage.
• Do not hide columns in functions. That is one of the fastest ways to make a query non-sargable.

Wrap-up

Common Mistakes:

• Guessing the fix before checking the plan. Correction: always inspect EXPLAIN or EXPLAIN ANALYZE first.
• Adding an index without matching the query shape. Correction: build the index around the real WHERE and ORDER BY.
• Wrapping indexed columns in functions. Correction: rewrite to a range or filter on the raw column.
• Ignoring write cost. Correction: remember that every index speeds reads only by charging more work on writes.

Memory Hook: ‘Catalog, not chaos’: the database should look up a short path, not rummage through every row.

Cheat Sheet:

• Measure first with EXPLAIN ANALYZE.
• Look for scans, sorts, bad joins, and row estimate mismatches.
• Keep predicates sargable.
• Use composite indexes that match filter + sort order.
• Remember index write overhead and stale statistics.
• Rewrite before you index when the query shape is the real problem.

Practice Tasks:

• Take one slow query from your last project and write the plan in plain English.
• Rewrite a function-based date filter into a range filter and explain why it is better.
• Create two indexes for the same query pattern and compare which column order works best.