The Magic of Explain Analyze
When database queries start slowing down, simply guessing which index to add is rarely effective. The EXPLAIN ANALYZE command instructs the database engine to parse your query, run it, and output the exact execution plan with timing logs.
Case Study: Slow Dashboard Order Lookups
An e-commerce order history dashboard began timing out for users with long order histories. The query looked simple, filtering by user ID and ordering by purchase date.
The Bug: Missing Composite Index
We ran EXPLAIN ANALYZE on the query:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 9481 ORDER BY purchase_date DESC;The output showed a sequential scan on a table containing 5 million rows, with a sorting operation happening in memory (disk sort fallback), taking 6.4 seconds to execute.
The Fix: Creating a Composite Index
We created a composite index covering both the filter column and the sorting column:
CREATE INDEX idx_orders_user_date ON orders(user_id, purchase_date DESC);Re-running the EXPLAIN command confirmed the engine switched to an Index Scan on the new index, reducing execution time to 1.1 milliseconds.