The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Indexes and execution plans

Reading the execution plan

What should you read in one line of EXPLAIN?

Put EXPLAIN in front of a query and the database shows the plan it has made for running that query. One line has many columns, but if you can read just four of them (type, key, rows, and Extra), you can find the cause of most slow queries.

Checked against real execution · All 50 combinations were run with EXPLAIN on MySQL 8.4 and checked against type, possible_keys, key, and Extra (for rows, only the size was checked)

Concepts in this lab

Query execution plan (EXPLAIN)

The plan that the database decides on for how to run a query. It shows which indexes are used, in what order the tables are joined, and how many rows the database expects to read. You view it by putting EXPLAIN in front of the query, and it is the starting point for finding the cause when a query is slow.

EXPLAIN join types (type column)

The column in MySQL EXPLAIN output that shows the method used to read the table. Roughly from the fewest rows read to the most, the values are const (one row through a primary key or unique index), eq_ref (in a join, one row for each row of the previous table), ref (the rows with the same value in a non-unique index), range (one range of an index), index (the whole index), and ALL (the whole table). Despite its name, index means that the index is read from beginning to end, so it is an access that reads everything, like ALL. Which value appears is decided by the optimizer based on statistics, so even the same query can differ depending on the amount and distribution of the data.

EXPLAIN Extra information (Extra column)

The column in MySQL EXPLAIN output that lists work done in addition to the way the table is read. Using where means that the rows that were read are filtered again by the condition, and Using index means that the query finishes by reading only the index, without going to the table. Using filesort means that a separate sort is done for ORDER BY, and Using temporary means that a temporary table is created for GROUP BY and the like. If the column is empty, no extra work is added. Using filesort and Using temporary can cause slowness when many rows are read, so look at them together with the value of the rows column.

Full table scan

A way of finding the rows that match a condition by reading every row of the table from beginning to end. It is chosen when no index can be used for the condition, or when an index exists but the optimizer judges that reading everything is better because too many rows match the condition. When it is used to find a few rows in a large table, it causes performance problems.

Covering index

An index that contains every column a query needs, so the result can be produced from the index alone without reading the table rows. Skipping the table access makes the read faster. In a MySQL execution plan, it appears as Using index in the Extra column.

Try these

• Default settings — The column has an index, but a function is applied to it, so type is ALL.
• Writing the same meaning with LIKE — LIKE 'user001%'. type changes to range.
• One row by primary key — type const. This is the case that reads the least.
• LIKE with a leading %, selecting only indexed columns — type index. Despite its name, it reads the whole index.
• Find with the index, sort separately — type is ref, but Using filesort appears in Extra.
• GROUP BY — Using temporary appears in Extra.
• Join — Two lines appear, and the type of the second line is eq_ref.

Running with the default settings

1. Run it with EXPLAIN: it returns only the execution plan, without actually running the query. The query reads one table, so one line appears. The member table has 20,000 rows, and the number of rows that actually match this condition is 200.
2. The type column is ALL: Every row of the table is read from beginning to end (a full table scan). When ALL appears for a query that looks for a few rows in a table with many rows, it is the first place to suspect. name has an index, but it could not be used to narrow the range. The index holds only the original values of name, not the results of computing LEFT(name, 7). The function would have to be computed for each row, so the range to read cannot be decided.
3. The possible_keys column and the key column: possible_keys lists the indexes that were considered usable for the condition, and key is the index that was actually chosen. Both are NULL, so no index is used. Although name has an index, it did not even make it onto the candidate list.
4. The rows column is about 20,000: the number of rows expected to be read. It is an estimate from statistics, not a counted value, so it can differ slightly. The result has 200 rows, but it reads the whole table. Most of the rows are read and then discarded.
5. In the Extra column, Using where: It means that the rows that were read are checked one by one against the WHERE condition, and the ones that do not match are filtered out. It signals that there is a condition that could not narrow the range using an index. If this appears when type is ALL, it means "everything is read, and then the rows that do not match the condition are discarded."

Result of this run

• A column that has an index cannot have its range narrowed by the index if a function or a calculation is applied to it. The rule is to leave the column as it is and change the value you compare it with. Here, name LIKE 'user001%' means the same thing and gives type range.
• Order of reading: look at type to see how widely it reads, check key to see which index is used, estimate the amount read with rows, and then look in Extra for added work such as filtering, sorting, and temporary tables.

Code

-- member table: 20,000 rows
-- primary key id, index idx_name (name)
-- no index on the age column

EXPLAIN
SELECT *
  FROM member
 WHERE LEFT(name, 7) = 'user001';

Assumptions of this model

For all 50 combinations, the SQL shown on the screen was run with EXPLAIN on MySQL 8.4 on the PC used to write this lab, and type, possible_keys, key, and Extra were compared character for character. The tables held 20,000 rows in member (two rows for each name) and 20,000 rows in orders, and ANALYZE TABLE was run before the queries. rows is a value estimated from statistics and differs slightly from run to run (on this PC, between 19,700 and 20,400 for a full scan), so only whether it is 1 row, some of the rows, or close to the whole table was compared. The number of rows that actually match the condition was also counted on the same data. An execution plan is chosen by the optimizer by calculating costs, so with a different number of rows or a different distribution of values, the same query can get a different plan. For example, in a table with only a few rows, or when a very large number of rows match the condition, ALL can be chosen even if an index exists (the explanation of full table scans in the MySQL documentation). The sentences that explain why nothing is shown in Extra (it is a single value, so no sort is needed, and so on) follow the explanation of ORDER BY optimization in the MySQL documentation. The id, select_type, partitions, key_len, and filtered columns are left out of the display.

Previous lab: Composite and covering indexes · Next lab: Join algorithms