Find the Slow Query Before Your Users Do
A single query that takes 40 ms is not a problem. A page that runs it four hundred times is. Slow pages are usually not caused by a slow query — they are caused by a normal query executed in a loop.
Turn on the slow query log first
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- where is it writing?
SHOW VARIABLES LIKE 'slow_query_log_file';
Come back in a day and read it. Two entries from real traffic tell you more than an hour of guessing.
Read EXPLAIN like a checklist
EXPLAIN SELECT a.id, a.title
FROM articles a
JOIN article_tags at ON at.article_id = a.id
WHERE at.tag_id = 12 AND a.status = 'published';
type—ALLmeans a full table scan. On a growing table, that is your answer.rows— the estimated rows examined. Multiply it by the number of times the query runs per page.Extra—Using temporaryorUsing filesorton a large result set usually means the index does not match theORDER BY.
EXPLAIN output, column by column
EXPLAIN prints one row per table in the join:
1 | SIMPLE | at | ref | idx_tag | 4 | 840 | 100.00 | Using index
1 | SIMPLE | a | ref | PRIMARY | 8 | 1 | 5.00 | Using where
type— best to worst:const,eq_ref,ref,range,index,ALL.ALLis a full table scan.rows— the estimate per loop of the join, so multiply by the outer table's rows: 840 outer rows against 1 inner row is 840 lookups.key_len— how many bytes of the index are used: 4 for anINT UNSIGNED NOT NULL, 9 for a nullableBIGINT. Akey_lenof 4 on a three-column index means only the first column is in play.Extra—Using indexmeans answered from the index alone;Using wheremeans filtered after reading;Using join buffermeans no usable index on the joined column.
From MySQL 8.0.18, EXPLAIN ANALYZE runs the query and prints measured values beside the estimates — actual time=0.05..2.1 rows=840 loops=1. Compare those rows with the estimate and multiply times by loops; an estimate off by two orders of magnitude means stale statistics, so run ANALYZE TABLE first.
Index the shape of your query
A composite index is read left to right, so put the equality columns first and the range or sort column last:
ALTER TABLE articles
ADD INDEX idx_articles_status_publish (status, publish_date);
That single index turns “filter by status, order by date” from a scan plus a sort into a range read in the correct order.
Composite index column order is not a style choice
Left to right is literal: (status, publish_date) answers WHERE status = … and WHERE status = … ORDER BY publish_date, and nothing for a query that filters on publish_date alone.
Two ways to lose an index without noticing: WHERE DATE(publish_date) = '2026-01-01' cannot use it, while publish_date >= '2026-01-01' AND publish_date < '2026-01-02' can; and using the highest-cardinality column leftmost instead of the one your queries filter on most often.
MySQL 8.0.13 can skip-scan a non-leading column, which is a fallback, not a design. Unused indexes still cost writes: SELECT * FROM sys.schema_unused_indexes;.
Covering indexes, and why yours gets ignored
Using index in Extra means the rows were never read. InnoDB secondary indexes carry the primary key, so (status, publish_date) already covers a query that selects id. Adding a wide VARCHAR column buys that read at the price of an index nearly as large as the table.
An index can also exist and still be ignored:
- Type mismatch.
WHERE user_id = '42'against a numeric column forces a conversion that can make the index unusable. - Low selectivity. If the optimiser expects more than roughly a fifth of the table, a scan beats random row lookups — the usual fate of an index on a three-value
status. - Leading wildcard or collation mismatch.
LIKE '%term'cannot use a B-tree prefix, and a collation mismatch disables the index on the joined side. - Stale statistics. Join order follows row estimates, and one bad estimate flips the plan.
Fix the loop, then the query
Before adding an index, count the queries. A page firing 200 queries has a fetching problem: batch them with one WHERE id IN (…) or a join, and the page gets faster even if every individual query stays exactly as it was.
An index makes one query faster. Batching removes 199 queries. Do both, in that order of impact.
N+1 is an application bug that looks like a database problem
Every query in an N+1 storm is fast, so the slow query log never mentions it. Rank by total time instead:
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e9 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
Fix it in the application: eager load (with(), includes(), selectinload), batch WHERE id IN (…) in chunks of 500 to 1 000, or join once and de-duplicate. A page rendering 300 article cards went from 301 queries and 610 ms to 2 queries and 45 ms. Chunk the list: 50 000 ids in one statement is slower than the loop it replaced.
Connection pool exhaustion looks identical from outside
Twenty workers with one connection each is fine until a slow query holds them for thirty seconds. Requests then queue in the pool while SHOW PROCESSLIST is idle and CPU is low; the tells are Threads_connected at max_connections and rising Aborted_connects. Set a statement timeout shorter than your HTTP timeout.
When the fix is caching or pagination, not an index
If a query runs in 1 ms on every page, indexing is finished with it — cache the result. A COUNT(*) over millions of InnoDB rows still reads an index, so a maintained counter beats a cleverer one.
Deep pagination is the classic fast-query, slow-page case: LIMIT 100000, 20 reads 100 020 rows and discards 100 000. Keyset pagination replaces it:
SELECT id, title FROM articles WHERE status = 'published'
AND (publish_date, id) < ('2026-01-01 00:00:00', 9182)
ORDER BY publish_date DESC, id DESC LIMIT 20;
That form uses INDEX idx_status_date_id (status, publish_date, id) and costs the same on page 5 000 as on page 1. The arithmetic: 400 queries of 40 ms is 16 seconds of database time per page view, and batching to 4 takes that to 160 ms.
Last updated 19 Sep 2026