Reading Query Plans

The query below looks up one transit card's taps on one day. It returns two rows, yet it took about 2.7 seconds. MySQL can print the steps it followed and the time each one took:

EXPLAIN ANALYZE
SELECT tap_id, tap_time, station, fare_cents
FROM taps
WHERE card_id = 'C-14217' AND day_no = 37;
Copy

Output:

-> Filter: ((taps.day_no = 37) and (taps.card_id = 'C-14217'))  (cost=487482 rows=47264) (actual time=900..2678 rows=2 loops=1)
    -> Table scan on taps  (cost=487482 rows=4.73e+6) (actual time=0.684..2502 rows=5e+6 loops=1)
Copy

Read the two lines from the bottom up. MySQL read every row of the table, 5e+6 or 5 million of them, in 2,502 milliseconds, and then a filter kept the 2 rows that matched. Reading every row of a table to find a few is called a full table scan.

The printout is a query plan: the sequence of steps the database chose to produce a result, with figures for each step. Every plan in this lesson, including the one above, was captured on MySQL 8.4, and every output is real.

The Table

The table is the taps table from Window Functions, with the same six columns, generated at 5 million rows instead of six. It holds 100 days of 50,000 taps each, made by 20,000 cards that tap two or three times a day. At the start, the only index is the primary key on tap_id.

SELECT COUNT(*) AS taps, COUNT(DISTINCT card_id) AS cards,
       MIN(day_no) AS first_day, MAX(day_no) AS last_day
FROM taps;
Copy

Output:

+---------+-------+-----------+----------+
| taps    | cards | first_day | last_day |
+---------+-------+-----------+----------+
| 5000000 | 20000 |         1 |      100 |
+---------+-------+-----------+----------+
Copy

EXPLAIN and Its Columns

EXPLAIN prints the plan that the optimizer, the part of MySQL that decides how to run a query, has chosen for a statement, with estimates instead of measured figures. Its default output is the traditional format, a table with one row for each table the query reads. The \G ending, a feature of the mysql client, prints that row one column per line:

EXPLAIN
SELECT tap_id, tap_time, station, fare_cents
FROM taps
WHERE card_id = 'C-14217' AND day_no = 37\G
Copy

Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: taps
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4726432
     filtered: 1.00
        Extra: Using where
Copy

Four columns matter most.

• type is how MySQL reaches the rows. ALL is a full table scan, which the manual calls normally not good. The table below lists other values.
• rows is the number of rows MySQL expects to examine. For InnoDB tables it is an estimate, which is why it says 4,726,432 instead of 5,000,000.
• filtered is the estimated percentage of those rows that pass the table's condition. Here 1.00 percent of 4,726,432 is about 47,264 rows, while the true answer is 2.
• Extra holds notes. Using where means the WHERE clause decides which rows are sent on. Other notes include Using filesort (an extra sorting pass), Using temporary (a temporary table holds a result), and Using index (every column comes from the index).

The Plan as a Tree

EXPLAIN FORMAT=TREE prints the same plan as a tree of steps, which MySQL calls iterators, and it is the only format that shows hash joins. Each step's input is the indented line below it, so the tree is read from the bottom up.

EXPLAIN FORMAT=TREE
SELECT tap_id, tap_time, station, fare_cents
FROM taps
WHERE card_id = 'C-14217' AND day_no = 37;
Copy

Output:

-> Filter: ((taps.day_no = 37) and (taps.card_id = 'C-14217'))  (cost=487482 rows=47264)
    -> Table scan on taps  (cost=487482 rows=4.73e+6)
Copy

Every line carries two estimates: cost, the optimizer's estimated execution cost, and rows, the number of rows the step is expected to return. The filter's estimate of 47,264 rows is the same calculation as the filtered column, 1 percent of the 4.73 million rows the scan expects to read.

EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the statement and prints the tree plan with measured figures beside the estimates. In the opening output, each line has actual time=A..B rows=N loops=L. A is the time in milliseconds to the step's first row, and B is the time spent in the step and the steps below it. N is the rows really returned, and L is how many times the step ran.

The scan line reads actual time=0.684..2502 rows=5e+6, so all 5 million rows passed through it in about 2.5 seconds. The filter returned its first match at 900 ms, because tap 1812344 sits about a third of the way through the table. Its row count shows the size of the estimate error: 47,264 expected, 2 returned.

The two commands suit different moments.

• EXPLAIN: estimates only. It is safe on any table, but its figures can be far off, as they were here.
• EXPLAIN ANALYZE: measured figures, at the cost of running the query. A report that runs for 40 minutes takes 40 minutes to analyze, so run plain EXPLAIN first.

Adding an Index

An index is a separate, sorted copy of chosen columns, with each entry pointing back to its row, so MySQL can find matching rows without reading the whole table. An index on (card_id, day_no) matches the opening query, because the query compares both columns with =:

CREATE INDEX idx_card_day ON taps (card_id, day_no);
Copy

EXPLAIN
SELECT tap_id, tap_time, station, fare_cents
FROM taps
WHERE card_id = 'C-14217' AND day_no = 37\G
Copy

Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: taps
   partitions: NULL
         type: ref
possible_keys: idx_card_day
          key: idx_card_day
      key_len: 48
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: NULL
Copy

The type changed from ALL to ref, the key column names the new index, and the estimate fell from 4,726,432 rows to 2. The measured plan confirms it:

EXPLAIN ANALYZE
SELECT tap_id, tap_time, station, fare_cents
FROM taps
WHERE card_id = 'C-14217' AND day_no = 37;
Copy

Output:

-> Index lookup on taps using idx_card_day (card_id='C-14217', day_no=37)  (cost=2.18 rows=2) (actual time=6.99..7 rows=2 loops=1)
Copy

The lookup returned both rows in 7 ms, against 2,678 ms for the scan.

An index has costs, and the scan is sometimes the better plan.

• Cost: every write maintains the index. The MySQL manual states that indexes add to the cost of inserts, updates, and deletes, because each index must be updated.
• Cost: only a leftmost prefix helps. A multiple-column index serves a condition on its first column, or its first and second columns, but not on day_no alone.
• When the scan wins: the condition matches many rows. The condition card_id > 'C-28000' matches 1,999 cards of 250 taps each, which is 499,750 rows or about 10 percent of the table. The optimizer still chose the scan, even with the index available:

EXPLAIN FORMAT=TREE
SELECT tap_id, station
FROM taps
WHERE card_id > 'C-28000';
Copy

Output:

-> Filter: (taps.card_id > 'C-28000')  (cost=474414 rows=1.02e+6)
    -> Table scan on taps  (cost=474414 rows=4.53e+6)
Copy

Indexes for a Window Query

Here is the running total from Window Functions, limited to day 37 and run with the (card_id, day_no) index in place:

EXPLAIN ANALYZE
SELECT tap_id, card_id,
       SUM(fare_cents) OVER (
         PARTITION BY card_id
         ORDER BY tap_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM taps
WHERE day_no = 37;
Copy

Output:

-> Window aggregate: sum(taps.fare_cents) OVER (PARTITION BY taps.card_id ORDER BY taps.tap_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   (cost=0 rows=4.73e+6) (actual time=3006..3021 rows=50000 loops=1)
    -> Sort: taps.card_id, taps.tap_id  (cost=493767 rows=4.73e+6) (actual time=3006..3008 rows=50000 loops=1)
        -> Filter: (taps.day_no = 37)  (cost=493767 rows=4.73e+6) (actual time=1060..2979 rows=50000 loops=1)
            -> Table scan on taps  (cost=493767 rows=4.73e+6) (actual time=0.746..2823 rows=5e+6 loops=1)
Copy

The index is not used, because the query has no condition on card_id, its first column. MySQL scans 5 million rows, keeps 50,000, sorts them, and computes the window in 3,021 ms.

The index this query needs lists its columns in the order the query uses them: the = filter column, then the PARTITION BY column, then the window's ORDER BY column. Adding the summed column makes it a covering index, an index that holds every column the query reads, so MySQL never visits the table rows:

CREATE INDEX idx_day_card ON taps (day_no, card_id, tap_id, fare_cents);
Copy

EXPLAIN ANALYZE
SELECT tap_id, card_id,
       SUM(fare_cents) OVER (
         PARTITION BY card_id
         ORDER BY tap_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM taps
WHERE day_no = 37;
Copy

Output:

-> Window aggregate: sum(taps.fare_cents) OVER (PARTITION BY taps.card_id ORDER BY taps.tap_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   (cost=0 rows=103616) (actual time=26.6..41.3 rows=50000 loops=1)
    -> Sort: taps.card_id, taps.tap_id  (cost=11130 rows=103616) (actual time=26.6..28.8 rows=50000 loops=1)
        -> Covering index lookup on taps using idx_day_card (day_no=37)  (cost=11130 rows=103616) (actual time=0.159..9.71 rows=50000 loops=1)
Copy

The scan is gone, and the total time fell from 3,021 ms to 41.3 ms. The Sort step is still there, however, even though the index already returns the rows of day 37 in card and tap order. A plain ORDER BY on the same columns, with the same index, has no Sort step:

EXPLAIN FORMAT=TREE
SELECT tap_id, card_id, fare_cents
FROM taps
WHERE day_no = 37
ORDER BY card_id, tap_id;
Copy

Output:

-> Covering index lookup on taps using idx_day_card (day_no=37)  (cost=11130 rows=103616)
Copy

On MySQL 8.4, then, the index removed the scan but not the window's sort. The lookup finished at 9.71 ms and the sort returned its first row at 26.6 ms, so sorting the 50,000 rows took about 17 ms. The sort was never the expensive step: in both plans it handled the same 50,000 rows, and the scan below it took almost 3 seconds.

The Warehouse View

A cloud warehouse such as BigQuery stores each column separately, and its on-demand price is set by the bytes of the columns a query reads. The figure to check is therefore bytes processed, which a dry run estimates before the query runs.

Suppose the taps table were loaded into BigQuery with card_id as a STRING and day_no and fare_cents as INT64. An INT64 value is 8 logical bytes, and a STRING is 2 bytes plus its UTF-8 length, so a card ID like C-14217 takes 9 bytes. A query that reads those three columns over 5 million rows processes 5,000,000 × (9 + 8 + 8) = 125,000,000 bytes.

• Partition pruning reduces bytes. A partitioned table is split into segments by ranges of one column, such as one range per value of the INTEGER day_no. With WHERE day_no = 37, BigQuery scans only the matching partition, which is called pruning: 50,000 rows × 25 bytes = 1,250,000 bytes.
• A small query is billed at a minimum. BigQuery bills at least 10 MB per query and per table referenced, so the pruned query is billed as 10 MB, still under a tenth of the full scan.
• LIMIT does not reduce bytes. On a table that is not clustered, LIMIT 10 does not change the data read, so the bill stays at 125,000,000 bytes.
• Clustering reduces the blocks read. A clustered table keeps its storage blocks sorted by chosen columns, so a filter on card_id in a table clustered by card_id scans only the relevant blocks.

Using This in an Interview

When asked why a query is slow, read the plan before changing anything, and say what each figure means. "The type is ALL and rows is 4.7 million for two returned rows, so this is a full table scan. An index on card and day should turn it into a ref lookup, and every insert will also have to update that index." Two follow-up questions are worth preparing for:

• "Why does your index not help the daily report?" A multiple-column index serves only conditions on its leftmost columns. A filter on day_no alone needs an index that starts with day_no.
• "Would LIMIT 10 make the warehouse query cheaper?" Not on a non-clustered BigQuery table, because the bytes read stay the same. A filter on the partitioning column is what reduces them.

Key Takeaways

• EXPLAIN shows the optimizer's plan with estimates; in the traditional format, read type, rows, filtered, and Extra first.
• FORMAT=TREE shows the plan as steps read from the bottom up, and EXPLAIN ANALYZE adds measured time and rows by running the query.
• Type ALL with a large rows figure for a small result means a full table scan, and an index whose leftmost columns match the = conditions turns it into a lookup.
• An index costs work on every write, and the optimizer still prefers a scan when a condition matches a large share of the table.
• On MySQL 8.4, a matching index removed the scan below a window but not the window's Sort step.
• In BigQuery, check bytes processed: partition pruning reduces them, while LIMIT on a non-clustered table does not.

A query plan explains where a SQL query spends its time inside the database. A large part of data work also happens outside a database, in code that reads files and records directly and has to be just as careful with time and memory. The next lesson, Python for Data Problems, turns to that work.

Practice Questions

1. An EXPLAIN row shows type ALL, rows 4,726,432, and filtered 1.00. What does type ALL mean, and about how many rows does the optimizer expect to pass the condition?

<details> <summary>Show answer</summary>

ALL is a full table scan, and the optimizer expects about 47,264 rows to pass. The filtered column is a percentage, so 1.00 percent of 4,726,432 rows is about 47,264. Both figures are estimates. In this lesson the real query returned only 2 rows, which only EXPLAIN ANALYZE could show.

</details>

2. The table has an index on (card_id, day_no). Which of these conditions can use it to look up rows: card_id = 'C-14217', day_no = 37, or card_id = 'C-14217' AND day_no = 37?

<details> <summary>Show answer</summary>

The first and the third, but not day_no = 37 alone. A multiple-column index can serve any leftmost prefix of its columns: card_id alone, or card_id with day_no. A condition on the second column alone has no starting point in the sorted index, so MySQL scanned the whole table for the day 37 window query.

</details>

3. A report query runs for 40 minutes. A colleague wants to check its plan with EXPLAIN ANALYZE. What will that cost, and what should be run first?

<details> <summary>Show answer</summary>

EXPLAIN ANALYZE runs the query, so it will take about 40 minutes. Run plain EXPLAIN first. It shows the access type, the index chosen, and the estimated rows from estimates alone, which is usually enough to spot a full table scan. Use EXPLAIN ANALYZE when the estimates look wrong and the measured figures are worth the wait.

</details>

4. After adding the covering index, the window query's plan still contains a Sort step. Was the index wasted?

<details> <summary>Show answer</summary>

No, because it removed the 5-million-row scan. The total time fell from 3,021 ms to 41.3 ms. MySQL 8.4 still sorted the rows for the window, but only the 50,000 rows of day 37, which took about 17 ms. Without the index, the sort waited on a scan of every row in the table.

</details>

5. In BigQuery, a query reads card_id (9 bytes per value) and fare_cents (8 bytes) from all 5 million taps and ends with LIMIT 100. The table is not clustered. How many bytes does it process?

<details> <summary>Show answer</summary>

5,000,000 × (9 + 8) = 85,000,000 bytes, and LIMIT does not change that. On a non-clustered table, LIMIT does not reduce the data read. To read less, filter on the partitioning column, for example one day_no, so that BigQuery prunes the other partitions.

</details>