Query performance with EXPLAIN

A query that takes 20 ms on your laptop can take 20 seconds on a production table with ten million rows. EXPLAIN shows how MySQL plans to run a query, so you can see whether it uses an index or scans every row.

Reading EXPLAIN output

EXPLAIN
SELECT id, total FROM orders
WHERE customer_id = 4521 AND status = 'PAID';

Column	What to look for
type	Access method. Good to bad: const, eq_ref, ref, range, index, ALL
possible_keys	Indexes MySQL considered
key	Index actually chosen (NULL means none)
rows	Estimated rows examined — smaller is better
Extra	Hints such as Using index, Using filesort, Using temporary

type = ALL on a large table means a full table scan. Using filesort and Using temporary mean extra sorting or temp tables, often fixable with a better index.

EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and reports real timings per step, which is more reliable than estimates.

Composite indexes and the leftmost-prefix rule

An index on (customer_id, status, created_at) is sorted by customer_id first, then status, then created_at. MySQL can use it for conditions on a leftmost prefix of those columns.

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

Query filter	Uses the index?
customer_id = ?	Yes
customer_id = ? AND status = ?	Yes
customer_id = ? AND status = ? ORDER BY created_at	Yes, and avoids filesort
status = ? alone	No (skips the first column)

Put equality columns first and range or sort columns last.

Covering indexes

If every column a query needs is inside the index, MySQL never touches the table rows. EXPLAIN shows Using index.

CREATE INDEX idx_orders_cust_total ON orders (customer_id, total);
SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id;   -- served from the index

Rewrites that unlock indexes

Wrapping an indexed column in a function hides it from the index.

-- Slow: function on the column forces a scan
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Fast: a range on the raw column can use an index on created_at
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Similarly, LIKE '%sharma' cannot use an index but LIKE 'sharma%' can, and comparing a VARCHAR column to a number causes an implicit cast that disables the index.

A performance checklist

1. Turn on the slow query log (long_query_time = 1) to find the real offenders.
2. Run EXPLAIN and look at type, key and rows.
3. Add or adjust an index that matches the WHERE, JOIN and ORDER BY columns.
4. Select only needed columns instead of SELECT *.
5. Paginate large lists with keyset pagination (WHERE id > last_seen ORDER BY id LIMIT 50) rather than huge offsets.
6. Re-run EXPLAIN ANALYZE to confirm the improvement.

Common mistakes

• Adding an index for every column separately instead of one well-ordered composite index.
• Indexing low-selectivity columns like is_active on their own.
• Forgetting that every extra index slows down inserts and updates.