Wiki

MySQL EXPLAIN

id

The SELECT identifier.

select_type

The SELECT type.

table

The table for the output row.

type

The join type, ordered from best to worst.

Name Bechreibung
system The table has only one row (= system table). This is a special case of the const join type.
const The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.

SELECT * FROM tbl_name
  WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1
  AND primary_key_part2=2;
eq_ref One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table.

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
ref All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator.

SELECT * FROM ref_table
  WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
fulltext The join is performed using a FULLTEXT index.
ref_or_null This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries.

SELECT * FROM ref_table
  WHERE key_column=expr
  OR key_column IS NULL;
index_merge This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.
unique_subquery This type replaces eq_ref for some IN subqueries.

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries.

value IN (SELECT key_column FROM single_table WHERE some_expr)
range Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators.

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10
  AND key_part2 IN (10,20,30);
index The index join type is the same as ALL, except that the index tree is scanned.

MySQL can use this join type when the query uses only columns that are part of a single index.
ALL A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

possible_keys

The possible indexes to choose.

key

The index actually chosen.

key_len

The length of the chosen key.

ref

The columns compared to the index.

rows

Estimate of rows to be examined.

Extra

Additional information.

Name Bechreibung
Using filesort MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order.
Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
Using index condition Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer ("push down") reading full table rows unless it is necessary.
Using temporary To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
Using where A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

Quellen

  • https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
  • https://mysqlexplain.com/learning/explain/type
  • https://oneuptime.com/blog/post/2026-03-31-mysql-explain-type-column/view
  • https://oneuptime.com/blog/post/2026-03-31-mysql-explain-extra-column/view
Stand: Nov. 17, 2026