Reading EXPLAIN Output

EXPLAIN shows the plan the optimiser picked before you wait for the query to run, and EXPLAIN ANALYZE shows what actually happened. Most people read it looking for the slow part; read the row counts instead, because the time is downstream of them.

EXPLAIN shows you the plan the optimiser picked, before you wait for the query to run. EXPLAIN ANALYZE runs it and shows what actually happened.

Most people read the output looking for the slow part. Read the row counts instead: the time is downstream of them.

The number that matters

EXPLAIN SELECT * FROM catalog_lessons WHERE course_id = 'c0000001-...';

id  select_type  table            type   key                rows   Extra
1   SIMPLE       catalog_lessons  ref    ix_lessons_course  12     NULL

type: ref with a key named means an index was used. rows: 12 is the optimiser's estimate of how many rows it will have to look at.

Now the same query without the index:

id  select_type  table            type   key    rows    Extra
1   SIMPLE       catalog_lessons  ALL    NULL   208     Using where

type: ALL is a full table scan, key: NULL says no index was used, and rows is the whole table. At 208 rows nobody notices. At eight million the page times out, and the query did not change - the data did.

When the estimate is a lie

EXPLAIN ANALYZE gives estimated and actual rows side by side:

-> Index lookup on l using ix_lessons_course  (cost=4.2 rows=12) (actual time=0.03..0.09 rows=1180 loops=1)

Estimated 12, actual 1180. The optimiser was working from stale statistics, and every decision it made downstream of that node - join order, whether to use an index at all - was made on a wrong number.

That is where to look first when a query is slow for no visible reason. ANALYZE TABLE refreshes the statistics; if the gap persists, the data is skewed in a way the sampled statistics cannot see.

What an index can and cannot serve

A composite index on (course_id, position) serves:

WHERE course_id = ?
WHERE course_id = ? ORDER BY position
WHERE course_id = ? AND position > ?

and does not serve WHERE position > ? on its own. An index is usable left to right, like a phone book sorted by surname then first name: useless for finding everyone called Ahmed.

Using filesort in the Extra column means the sort could not be served by an index and the rows are being sorted after the fact. Using temporary means an intermediate table was materialised. Neither is fatal; both are worth knowing about before they are on a million rows.

The order to work in

Find the query, with the slow query log or your APM - not by reading the code and guessing.
EXPLAIN it, and read the row counts.
Fix the access path: add the index, or change the query so an existing one can be used.
Measure again. An index that was not used is a write cost with no read benefit.