Slow queries often look harmless until concurrency turns them into an outage. EXPLAIN reveals the execution plan chosen by the MySQL or MariaDB optimizer, making full scans, wasteful joins, and avoidable sorts visible before tuning devolves into guesswork.
Running EXPLAIN prefixes a plan table to a statement, typically returning one row per table (or subquery) that participates in execution. Columns like type, possible_keys, key, rows, filtered, and Extra describe the access method, index candidates, index choice, estimated work, and extra operations performed.
Plan output is an estimate based on statistics, data distribution, and the specific constants used in predicates, so different parameter values can produce different plans. Statistics can drift after bulk loads or large deletes, and optimizer behavior can change across major version upgrades, so plans merit a second look after churn. Some servers provide EXPLAIN ANALYZE to execute the statement and report actual timing, which is useful for validation but risky on busy production workloads.
Steps to use EXPLAIN to view query plans:
- Pick a slow query to analyze from the slow query log or application traces.
- Run EXPLAIN for the query using representative constants.
mysql> EXPLAIN SELECT id, email FROM users WHERE last_name = 'Smith' ORDER BY created_at DESC LIMIT 50; +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------------+ | id | select_type | TABLE | partitions | TYPE | possible_keys | KEY | key_len | REF | ROWS | filtered | Extra | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------------+ | 1 | SIMPLE | users | NULL | REF | idx_users_last_name | idx_users_last_name | 202 | const | 1240 | 100.00 | USING WHERE; USING filesort | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------------+ 1 ROW IN SET, 1 warning (0.00 sec)
Standard EXPLAIN reports estimates without executing the statement.
- Treat each EXPLAIN row as one access operation within the statement.
Single-table statements usually return one row. Joins usually return one row per table. Row order reflects optimizer join order.
- Confirm index usage by comparing possible_keys with key for each row.
possible_keys lists candidate indexes. key shows the chosen index. key being NULL usually indicates no usable index for that step.
- Use the type column to spot scans versus targeted lookups.
Common values: ALL (full scan), index (full index scan), range (range scan), ref (non-unique lookup), eq_ref (unique lookup). ALL can be acceptable for genuinely small tables.
- Use rows with filtered to estimate how much data is examined before conditions discard it.
Large rows with low filtered often indicates poor selectivity, stale statistics, or a missing index.
- Inspect Extra for operations that commonly dominate runtime.
Look for Using temporary, Using filesort, Using join buffer, Using index condition, and Using index for group-by when latency spikes.
- Review optimizer notes with SHOW WARNINGS after EXPLAIN when warnings are reported.
mysql> SHOW WARNINGS\G *************************** 1. ROW *************************** Level: Note Code: 1003 Message: /* select#1 */ SELECT `users`.`id` AS `id`,`users`.`email` AS `email` FROM `users` WHERE (`users`.`last_name` = 'Smith') ORDER BY `users`.`created_at` DESC LIMIT 50
The note often shows a rewritten form of the statement after parsing and optimization.
- Refresh statistics with ANALYZE TABLE when the plan looks out of sync with reality.
mysql> ANALYZE TABLE users; +-------------+---------+----------+----------+ | TABLE | Op | Msg_type | Msg_text | +-------------+---------+----------+----------+ | app.users | analyze | STATUS | OK | +-------------+---------+----------+----------+ 1 ROW IN SET (0.03 sec)
Fresh statistics can change possible_keys, key, rows estimates, and join order.
- Use EXPLAIN FORMAT=JSON to surface details hidden behind the tabular summary.
mysql> EXPLAIN FORMAT=JSON SELECT id, email FROM users WHERE last_name = 'Smith' ORDER BY created_at DESC LIMIT 50\G *************************** 1. ROW *************************** EXPLAIN: { "query_block": { "select_id": 1, "ordering_operation": { "using_filesort": TRUE, "table": { "table_name": "users", "access_type": "ref", "possible_keys": [ "idx_users_last_name" ], "key": "idx_users_last_name", "rows_examined_per_scan": 1240, "filtered": 100, "attached_condition": "(`users`.`last_name` = 'Smith')" } } } } ##### snipped #####
Some versions also provide EXPLAIN FORMAT=TREE for a human-readable plan tree.
- Use EXPLAIN ANALYZE when supported to collect actual timing and row counts per plan stage.
mysql> EXPLAIN ANALYZE SELECT id, email FROM users WHERE last_name = 'Smith' ORDER BY created_at DESC LIMIT 50; +--------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------+ | -> LIMIT: 50 ROW(s) (actual TIME=0.12..0.14 ROWS=50 loops=1) -> Sort: users.created_at DESC, LIMIT INPUT TO 50 ROW(s) per chunk (actual TIME=0.12..0.13 ROWS=50 loops=1) -> INDEX lookup ON users USING idx_users_last_name (last_name='Smith') (actual TIME=0.02..0.10 ROWS=1240 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 ROW IN SET (0.14 sec)
EXPLAIN ANALYZE executes the query, so run it during low traffic or against a replica when possible.
- Create a composite index that matches the filter column plus the sort column when Using filesort appears in Extra.
mysql> CREATE INDEX idx_users_last_name_created_at ON users (last_name, created_at); Query OK, 0 ROWS affected (2.41 sec) Records: 0 Duplicates: 0 Warnings: 0
Every index adds write amplification, disk usage, and buffer pool pressure, so keep only indexes that pay rent.
- Re-run EXPLAIN for the same constants to confirm the plan improves.
mysql> EXPLAIN SELECT id, email FROM users WHERE last_name = 'Smith' ORDER BY created_at DESC LIMIT 50; +----+-------------+-------+------------+------+-------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+ | id | select_type | TABLE | partitions | TYPE | possible_keys | KEY | key_len | REF | ROWS | filtered | Extra | +----+-------------+-------+------------+------+-------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | REF | idx_users_last_name,idx_users_last_name_created_at | idx_users_last_name_created_at | 207 | const | 50 | 100.00 | USING WHERE | +----+-------------+-------+------------+------+-------------------------------------------------------+----------------------------------+---------+-------+------+----------+-------------+ 1 ROW IN SET, 1 warning (0.00 sec)
- Validate the change by measuring runtime on representative parameters outside the EXPLAIN output.
Application timings, controlled benchmarks, or EXPLAIN ANALYZE on a safe target provide confirmation beyond estimates.
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
Comment anonymously. Login not required.