MySQL Query Optimization

Find the expensive queries first

The sys schema aggregates Performance Schema data into readable views:

-- Ranked by total latency, which is what actually consumes the server.
SELECT query, exec_count, total_latency, avg_latency, rows_sent_avg, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;
 
-- Statements doing full table scans.
SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;

rows_examined_avg far exceeding rows_sent_avg is the clearest indicator of a missing or mis-ordered index: the server is reading rows only to discard them.

EXPLAIN

EXPLAIN
SELECT o.id, o.total_cents, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at >= NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC LIMIT 50;

Columns to read, in order of usefulness:

• type — the access method. From best to worst: const, eq_ref, ref, range, index, ALL. ALL is a full table scan; index is a full index scan, which is only slightly better.
• key — the index actually chosen. NULL means none.
• rows — the optimiser's estimate of rows examined. Compare it with reality.
• filtered — the percentage expected to survive the WHERE. A low value with a high rows means most of the work is wasted.
• Extra — the important one:
  • Using index — covering index, no table access. Good.
  • Using where — rows filtered after reading. Usually fine.
  • Using filesort — a sort that the index did not provide.
  • Using temporary — a temporary table, common with GROUP BY on an unindexed expression.

For what actually happened rather than what was estimated, use EXPLAIN ANALYZE (MySQL 8.0.18+), which executes the statement and reports real timings per iterator.

EXPLAIN ANALYZE SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;   -- includes cost estimates and index usage detail

Rewrites that reliably help

Deep pagination. LIMIT 100000, 20 reads and discards 100,000 rows. Use keyset pagination:

-- Instead of OFFSET, remember the last row seen.
SELECT id, created_at FROM orders
WHERE customer_id = ? AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Functions on indexed columns. WHERE DATE(created_at) = '2026-07-30' cannot use an index on created_at. Use a range predicate instead.

SELECT * on wide tables. It defeats covering indexes and moves unnecessary bytes. Name the columns.

Correlated subqueries in the select list. Frequently rewritable as a join or a lateral derived table, turning N executions into one.

OR across different columns. Often better as a UNION ALL of two indexable queries.

Statistics and the optimiser

InnoDB estimates cardinality by sampling index pages. After large data changes:

ANALYZE TABLE orders;

Persistent statistics are on by default; innodb_stats_persistent_sample_pages controls sampling depth. Raise it for tables where the optimiser consistently misestimates.

If the optimiser keeps choosing a worse index, prefer fixing statistics or the index design over forcing:

-- Diagnostic, or a last resort. It stops adapting when the data changes.
SELECT * FROM orders FORCE INDEX (orders_customer_created) WHERE ...;

Optimizer hints (/*+ INDEX(...) */, /*+ JOIN_ORDER(...) */) are more targeted than FORCE INDEX and are the better choice when a hint is genuinely required.