HowToCodePage

Query optimisation and diagnostics

Find the queries that actually cost time, read plans and hints without guessing, and know which server settings matter more than any query rewrite.

Find the expensive queries first

Optimising by intuition wastes effort. The slow query log and the statement digest table both rank queries by real cost, which is the only ranking that matters.

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

SELECT DIGEST_TEXT,
       COUNT_STAR,
       ROUND(AVG_TIMER_WAIT / 1e9, 2)  AS avg_ms,
       ROUND(SUM_TIMER_WAIT / 1e9, 2)  AS total_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SELECT * FROM sys.statement_analysis LIMIT 10;
  • Rank by total time, not by average. A 20 ms query executed a million times costs far more than one two-second report.
  • Compare SUM_ROWS_EXAMINED against SUM_ROWS_SENT. A large ratio means the server is reading far more than it returns, which is the signature of a missing or unusable index.
  • The digest table normalises literals, so two calls that differ only in their constants collapse into one row — exactly what you want when deciding what to fix.

Reading plans and using hints sparingly

EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS n
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-01-01'
GROUP BY c.name;

EXPLAIN FORMAT=TREE
SELECT id FROM orders WHERE customer_id = 42;

SELECT /*+ JOIN_ORDER(c, o) NO_INDEX_MERGE(o) */ c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id;
Symptom in the planLikely cause
type: ALL with a large rowsNo usable index, or a function or type mismatch on the filtered column
Using filesort on a large setThe sort order is not supplied by an index
Using temporaryGROUP BY or DISTINCT over columns no index orders
Estimated rows far below actualStale statistics; run ANALYZE TABLE
Using where on a large row countRows are read and then discarded by the server

EXPLAIN ANALYZE runs the query and reports actual rows and time per node, which is the only way to see where the estimate diverged from reality. On a production primary, wrap it in a transaction and roll back, or run it on a replica.

Server-side levers

  • innodb_buffer_pool_size is the one setting that changes everything. If the working set does not fit, every query pays disk latency and no rewrite will save it.
  • innodb_io_capacity and innodb_buffer_pool_instances only matter after the pool is sized correctly.
  • tmp_table_size and max_heap_table_size decide when an in-memory temporary table spills to disk. Raising both together is what makes the change take effect.
  • join_buffer_size and sort_buffer_size are allocated per connection. A large value multiplied by hundreds of connections is a memory outage waiting to happen.
  • performance_schema has real overhead. Enable the consumers you actually read rather than all of them, especially on a busy server.
⚠️
An optimizer hint is a decision frozen at the moment you knew least. Fix the index, the statistics or the query instead. If a hint is genuinely required, leave a comment recording the query and the date, and re-test it after every version upgrade.

FAQ

Why was the query fast yesterday and slow today?
Usually stale statistics after a large load, a change in data distribution that made a previously selective index useless, a table that grew past a plan threshold, or lock contention from a long-running transaction. Check the plan first, then the locks.
Should I add an index for every slow query?
No. First check whether an existing index can serve a rewritten predicate, or whether the application is simply asking for too many rows. An index is the right answer when the access pattern is a genuine seek that the current schema cannot provide.

Advanced indexing: composite, covering and FULLTEXT Indexes and reading EXPLAIN

Last refreshed 2026-09-18.