Using MySQL EXPLAIN to Diagnose Slow Queries

For years my approach to a slow query was to add an index and hope. It works about half the time, and the other half you've made writes slower for nothing. Prefix the statement with EXPLAIN and MySQL tells you exactly what it plans to do. Reading that output well is the most useful tuning skill I know.

The examples are from MySQL 5.7, where EXPLAIN also works on INSERT, UPDATE and DELETE. Nearly everything applies to 5.6 and MariaDB 10.1 too.

First, find the queries worth explaining

You need candidates, and the slow query log is the honest source. Enable it at runtime:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 0;
SHOW VARIABLES LIKE 'slow_query_log_file';

Let it run through a busy period, then summarize it. mysqldumpslow ships with the server:

mysqldumpslow -s t -t 10 /var/lib/mysql/db1-slow.log

-s t sorts by total time, -t 10 shows the top ten. If you have Percona Toolkit, pt-query-digest gives a far richer report and I'd use it over this. mysqldumpslow is fine to start.

Run EXPLAIN on a real statement

Use the real parameter values from the log, not placeholders. The optimizer's choices depend on the actual values, and I've been fooled by this more than once.

EXPLAIN SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.created_at >= '2017-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

Typical output when things are bad:

id  select_type  table  type  possible_keys  key   rows    filtered  Extra
1   SIMPLE       o      ALL   NULL           NULL  1842211 1.11      Using where; Using filesort
1   SIMPLE       c      eq_ref PRIMARY       PRIMARY 1     100.00    NULL

Which columns matter

type (access method) is the first thing to look at. From best to worst: system, const, eq_ref, ref, range, index, ALL. ALL is a full table scan. index is a full scan of an index, which is not much better on a large table. range, ref and eq_ref are what you want for tables with more than a few thousand rows.

rows is the optimiser's estimate of how many rows it will examine for that step. Multiply the rows values down the plan to get a feel for total work. Here the orders table is scanned in full, 1.8 million rows, to return 50.

key is the index actually chosen; possible_keys lists what the optimiser considered. NULL in key with a non-empty possible_keys usually means the index exists but the statistics or the query shape made it look unattractive.

filtered (5.7) is the estimated percentage of rows that survive the WHERE after the access method. A low number with type: ALL means most of the scan is thrown away.

Extra carries the warnings. Using filesort means an extra sort pass because no index provides the requested order. Using temporary means an internal temporary table, often from GROUP BY or DISTINCT. Using index is good: the query was satisfied from the index alone. Using index condition means index condition pushdown is helping.

Building the index

The query filters on status (equality) and created_at (range), and sorts by created_at. The rule for a composite index is: equality columns first, then the range or sort column. One index can serve both the filter and the ORDER BY here:

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

Re-run the EXPLAIN:

id  select_type  table  type   possible_keys       key                rows  filtered  Extra
1   SIMPLE       o      range  idx_status_created  idx_status_created 3120  100.00    Using index condition
1   SIMPLE       c      eq_ref PRIMARY             PRIMARY            1     100.00    NULL

The scan dropped from 1.8 million rows to about three thousand and Using filesort is gone because the index is already ordered by created_at within each status.

When the plan is unclear

EXPLAIN FORMAT=JSON shows cost estimates and which part of the WHERE is applied where:

EXPLAIN FORMAT=JSON SELECT ... \G

Look for "attached_condition" and "used_key_parts". If only the first column of your composite index appears in used_key_parts, the rest is not being used, usually because of a data type mismatch or a function wrapped around the column.

On MySQL 5.7 you can also explain a statement that is already running, which is invaluable for the "what is it doing right now" question:

SHOW PROCESSLIST;
EXPLAIN FOR CONNECTION 4132;

Don't trust estimates alone

EXPLAIN gives estimates, so I confirm with actual counters. Before and after adding the index, run the query and check what it touched:

FLUSH STATUS;
SELECT ... ;  -- the real query
SHOW SESSION STATUS LIKE 'Handler_read%';

Handler_read_rnd_next counts rows read during a table scan; Handler_read_next counts index range reads. A good index turns a huge Handler_read_rnd_next into a small Handler_read_next. Also compare Created_tmp_disk_tables and Sort_rows if the plan mentioned temporary tables or filesort.

Why the index isn't used

Index exists but type: ALL persists. Check for implicit conversions: WHERE customer_id = '123' on an INT column is fine, but WHERE phone = 5551234 on a VARCHAR column forces a cast on every row and defeats the index. Also check for functions on the column, like WHERE DATE(created_at) = '2017-10-01'; rewrite as a range.

rows estimates wildly off. Statistics are stale. Run ANALYZE TABLE orders;. On InnoDB you can also raise innodb_stats_persistent_sample_pages for tables with skewed distributions.

Using filesort even with an index on the sort column. Sorting DESC on one column while filtering on another, or mixing ASC and DESC in the ORDER BY, prevents index use on 5.7. Reorder the index columns or accept the sort for small result sets.

Optimiser picks the wrong index. As a last resort, FORCE INDEX (idx_status_created) in the FROM clause. Treat it as a bug report against your schema or statistics, not a permanent fix.

I read type, rows, key and Extra, in that order, every time. Most slow queries I meet are fixed by that loop.