The EXPLAIN statement provides information about how MySQL executes queries. By prefixing a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement with EXPLAIN, the optimizer reveals the execution strategy, which helps identify bottlenecks and optimize indexing.

Output Columns

When executing an EXPLAIN command, MySQL returns a table containing the following columns:

Identifier (id)

The id indicates the sequence in which the query optimizer executes the SELECT operations.

1. Identical IDs: Tables are joined and executed in order from top to bottom.

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

2. Different IDs: Indicates subqueries. The larger the ID value, the higher the priority for execution.

EXPLAIN SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE email = 'test@example.com');

3. Mixed IDs: For a mix of identical and different IDs, groups with the same ID are executed sequentially, while the group with the larger ID executes first.

Query Type (select_type)

This column defines the specific role of each SELECT within the query structure.

• SIMPLE: No subqueries or unions.
• PRIMARY: The outermost query containing subqueries.
• SUBQUERY: The first SELECT inside a subquery.
• DERIVED: A subquery in the FROM clause, generating a temporary derived table.
• UNION: The second or later SELECT in a UNION.

-- DERIVED example
EXPLAIN SELECT * FROM (SELECT id FROM orders WHERE status = 'pending') AS derived_table;

-- UNION example
EXPLAIN SELECT id FROM orders WHERE status = 'shipped' UNION SELECT id FROM orders WHERE total > 1000;

Access Type (type)

The type column reveals the efficiency of data access. Performance generally degrades from best to worst as follows:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system: The table has only one row (system table).
• const: At most one matching row, typically found via a PRIMARY KEY or UNIQUE index.

EXPLAIN SELECT * FROM customers WHERE id = 100;

• eq_ref: One row from this table is read for each row combination from the previous tables. Used for indexed lookups using PRIMARY KEY or UNIQUE NOT NULL.

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

• ref: All rows matching an index value are read. Used with non-unique indexes or prefix matches.

EXPLAIN SELECT * FROM orders WHERE customer_id = 50;

• range: Retrieves rows within a specific range using an index.

EXPLAIN SELECT * FROM products WHERE price BETWEEN 10 AND 50;

• index: Full index scan. Slightly better than ALL because index files are usually smaller than data files.
• ALL: Full table scan. Indicates a lack of proper indexing, often requiring optimization.

Indexes (possible_keys, key, key_len)

• possible_keys: Indexes MySQL considered using.
• key: The index actually selected. If NULL, no index was used.
• key_len: Bytes of the index used. Shorter lengths are better for efficiency.

Rows and Filtering

The rows column estimates the number of rows MySQL must examine. Lower values indicate better query performance. filtered shows the percentage of rows filtered by the WHERE clause or JOIN conditions.

Additional Information (Extra)

The Extra field contains critical optimization clues.

• Using index: The query is covered by an index, meaning data is retrieved directly from the index without accessing the data rows.
• Using where: The WHERE clause is used to filter rows after they are retrieved.
• Using temporary: MySQL creates a temporary table to hold results, common in GROUP BY operations.

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;

• Using filesort: MySQL must perform an extra pass to sort the data, indicating the sort operation cannot be handled by an index.

EXPLAIN SELECT * FROM products ORDER BY created_at;

• Using join buffer: Indicates the use of a memory buffer for joins that lack indexes, often suggesting a need for optimization.