MySQL Indexing and EXPLAIN: Making Slow Queries Fast

Most slow MySQL queries are missing an index or can't use the one that exists. EXPLAIN shows you which.

Find slow queries

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;   -- seconds

Or query performance_schema.events_statements_summary_by_digest for the statements using the most total time.

Read EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

• type: ALL is a full table scan (bad on big tables); ref, range, eq_ref and const use indexes.
• key: the index actually chosen (NULL = none).
• rows: estimated rows examined.
• Extra: watch for Using filesort and Using temporary; Using index means a covering index (good).

EXPLAIN ANALYZE SELECT ...;   -- runs the query and shows actual time and rows per step

Composite index order

CREATE INDEX idx_orders_cust_status_created ON orders (customer_id, status, created_at);

This index serves the query above completely: equality on customer_id and status, then ordered by created_at. MySQL uses an index from the leftmost column, so it also helps queries on customer_id alone, but not on status alone.

Things that stop an index being used

WHERE YEAR(created_at) = 2026            -- function on the column
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'   -- index-friendly

WHERE phone = 9876543210                 -- string column compared to a number: implicit cast
WHERE phone = '9876543210'

WHERE name LIKE '%kumar'                 -- leading wildcard

Covering indexes

If the index contains every column the query needs, MySQL never reads the table rows. In InnoDB, secondary indexes include the primary key automatically, so SELECT id, status FROM orders WHERE customer_id = ? is covered by (customer_id, status).

Balance

Every index slows writes and takes space. Check sys.schema_unused_indexes and remove indexes nothing uses.