How to Use EXPLAIN in MySQL to Understand and Speed Up Queries

Home » Python » How to Use EXPLAIN in MySQL to Understand and Speed Up Queries

If you work with MySQL long enough, sooner or later you’ll meet a slow query that ruins your day. Maybe a report that never finishes, an API endpoint that times out under load, or a dashboard that suddenly takes 20 seconds to load. When that happens, the first real debugging tool you should reach for is the EXPLAIN family of commands.

MySQL’s EXPLAIN and EXPLAIN ANALYZE show you how the optimizer plans and actually executes a query. Used well, they reveal which tables are scanned, which indexes are used (or ignored), how joins are performed, how many rows are examined, and where time is really being spent. In this guide we’ll break down, in plain English, how to use them, how to read their output, and how to turn that insight into concrete performance improvements.

What EXPLAIN does in MySQL (and when to use it)

In MySQL, the EXPLAIN keyword is a diagnostic tool that shows the planned execution strategy for a statement instead of running it normally. You place EXPLAIN in front of your query, and MySQL responds with a small table describing how it intends to get the requested rows.

You can use EXPLAIN with SELECT, INSERT, UPDATE, DELETE and REPLACE statements, not just SELECT. For example:

Example:

Query example: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Instead of returning employee data, MySQL returns a row-by-row explanation of how it would execute that statement. You’ll see which table is read, which index is chosen, how many rows MySQL expects to inspect, and extra notes such as whether a temporary table or filesort is required.

Use EXPLAIN whenever you suspect a query is slower than it should be, or when you’re designing indexes and want to verify that MySQL will actually use them. It’s also extremely useful when you inherit complex legacy SQL with multiple subqueries and joins and you have to reverse‑engineer what the optimizer is doing.

Variations of EXPLAIN: EXTENDED, PARTITIONS, ANALYZE and formats

The basic EXPLAIN is only the starting point; MySQL offers several extensions and output formats that give more insight. Understanding these options helps you choose the right level of detail for each situation.

EXPLAIN EXTENDED adds more optimizer information, especially the filtered column and rewritten query text. After running EXPLAIN EXTENDED ... you can execute SHOW WARNINGS; to see how the optimizer rewrote your query internally, which is very handy for understanding optimization decisions.

EXPLAIN PARTITIONS displays which partitions of a partitioned table will be accessed by the query. The partitions column lists the relevant partitions, helping you verify that partition pruning is actually happening and you’re not hitting every partition by accident.

Output format is also flexible: you can get tabular, tab-separated, vertical, or JSON output in the CLI, and visual plans in tools like MySQL Workbench. JSON output is especially useful for automation and deeper analysis, because it includes cost estimates and nested plan structure, but note that plain EXPLAIN ANALYZE in MySQL currently does not support raw JSON output.

Visual tools like MySQL Workbench can render EXPLAIN as a graphical tree, which is often easier to understand for complex joins and subqueries. External tools such as Percona Toolkit, EverSQL, or Releem can consume EXPLAIN output or slow query logs and help you prioritize which statements to optimize first.

Understanding the EXPLAIN columns (table format)

When you run a classic EXPLAIN without JSON format, MySQL returns one row per table or subquery involved in your statement. The order of these rows matters: it shows the sequence in which tables are accessed during execution.

These are the key columns you will see and what they tell you:

• id: A sequential identifier for each part of the query. A single simple SELECT usually has id = 1. Multiple ids indicate subqueries, derived tables, or UNION parts. Higher numbers are generally executed before lower ones, giving you a hint of execution order.
• select_type: Describes the role of that SELECT within the overall query, with values like SIMPLE (no subqueries or UNIONs), PRIMARY (outermost SELECT in a complex query), UNION, UNION RESULT, DERIVED (subquery in FROM), or fulltext‑related types. This lets you see at a glance if you’re dealing with nested queries, unions or derived tables.
• table: Indicates which table or internal result this row refers to. It can be a real table name, or a pseudo-name like <unionM,N> for an internal UNION result, <derivedN> for a derived table, or similar markers for materialized subqueries.
• partitions: When using partitioned tables together with EXPLAIN PARTITIONS, this lists the partitions that contain rows matching the query conditions. If you see many partitions listed, you may not be benefiting from partition pruning.
• type: Often called the join or access type, this is one of the most important indicators of performance. It describes how MySQL accesses rows: values range from very efficient (like const, eq_ref, ref) to less efficient (range) to poor (index, ALL full table scan). Special types like index_merge, unique_subquery, and index_subquery indicate specific optimizations.
• possible_keys: Lists indexes that MySQL considers could be used for this part of the query. If this is NULL, it means MySQL sees no useful index, which is a strong signal that you might need to create one based on your WHERE or JOIN conditions.
• key: Shows the actual index chosen by the optimizer for this access. If it’s NULL while possible_keys lists candidates, the optimizer decided that using an index was not worth it, often due to low selectivity or small table size.
• key_len: Shows the number of bytes of the index that are used. For composite indexes, this indicates how many leading columns are effectively in play. This is crucial for understanding whether your multi-column index is being used fully or only partially.
• ref: Indicates what is being compared to the index columns listed in key: it may point to another table column (for joins) or to a constant (for simple filters).
• rows: An estimate of how many rows MySQL expects to examine for this step. It is approximate, based on statistics, but very useful to gauge how expensive a query will be and whether an index is reducing the search space effectively.
• filtered: Available with EXPLAIN EXTENDED, this percentage represents how many of the examined rows are expected to pass the conditions on that table. Low percentages combined with high rows often scream for better indexing or more selective conditions.
• Extra: A free-form field containing extra notes that do not fit elsewhere, such as Using index, Using where, Using temporary, Using filesort, fulltext hints, and more, all of which hold important performance clues.

By reading these columns together instead of in isolation, you get a compact but powerful summary of how MySQL intends to satisfy your query. With a bit of practice, you’ll quickly spot red flags like full table scans, missing indexes, or unnecessary temporary tables.

How EXPLAIN defines access types (the type column)

The type column deserves special attention because it’s one of the fastest ways to judge the health of a query. Although documentation calls it a join type, it is more accurate to think of it as an access type describing how rows are found.

At a high level, access types range from “great” to “terrible” in terms of performance. While the exact list is long, important ones include:

• const / system: MySQL can resolve the table to at most one row via a primary key or unique index with a constant value. This is extremely efficient.
• eq_ref: For each row from the previous table, MySQL reads exactly one matching row from this table, usually due to a unique or primary key reference in a join.
• ref: MySQL accesses rows via a non-unique index matching a constant or column; multiple rows may match. Still generally good.
• range: MySQL uses an index to retrieve rows in a given range of values (e.g., BETWEEN, >=, or a prefix match with LIKE 'abc%'). This is often acceptable, especially for date or numeric ranges.
• index: MySQL scans the entire index instead of the full table data. Better than a full table scan but still often too expensive on large indexes.
• ALL: Full table scan. MySQL reads every row to find matches. On small tables this can be fine, but on large tables you should generally consider indexing or query rewrites to avoid it.
• index_merge: Indicates that MySQL is combining results from multiple indexes on the same table. It sounds nice, but in practice it often performs worse than a good single composite index.
• unique_subquery / index_subquery: Special access types used to optimize certain IN (SELECT ...) subqueries via uniqueness or non-unique index lookups, replacing less efficient lookup patterns.

When you review EXPLAIN output, you generally want access types to be as far toward const / eq_ref / ref as possible and avoid ALL on large tables. If you see ALL with a high rows estimate, it’s almost always a candidate for better indexing.

Key, possible_keys and key_len: are your indexes really helping?

Indexes are often the single most powerful way to speed up queries, and EXPLAIN helps you understand exactly how they are being used. Three columns are especially important for index diagnostics: possible_keys, key, and key_len.

possible_keys lists all the indexes MySQL thinks could be relevant for that table based on the WHERE and JOIN conditions. If this is NULL for a table that participates in a heavy filter or join, that’s a strong hint that you should consider adding an index that covers the relevant columns.

key tells you which index MySQL actually picked from the candidates. If key is NULL while there are entries in possible_keys, the optimizer decided a full scan is cheaper, which usually indicates poor index selectivity, outdated statistics, or a query pattern that cannot benefit from the available index design.

key_len shows how many bytes of the chosen index are used. For composite indexes, this lets you confirm how many of the indexed columns are contributing to the search. If you built an index on (last_name, first_name) and key_len only covers the length for last_name, you know that queries are not taking full advantage of the composite index.

The ref column complements this by telling you what MySQL is comparing against the index, whether it’s a constant (like 'Puppo') or a column from a joined table. If you are debugging a join, seeing the correct join column in ref together with a good type value such as eq_ref is a sign that your join is well indexed.

Rows, filtered and Extra: spotting hidden inefficiencies

The rows and filtered columns provide a rough idea of work volume, while Extra highlights special operations that often explain performance problems. These three should always be reviewed together.

rows is MySQL’s estimate of how many records it must examine at this step. It is not always exact, but it’s good enough to detect obviously bad cases, such as scanning hundreds of thousands of rows when you expected a handful.

filtered (available through EXPLAIN EXTENDED) gives the estimated percentage of examined rows that will pass conditions for that table. A high rows with a very low filtered percentage usually points to missing or suboptimal indexes or poorly selective predicates.

The Extra column aggregates additional notes about the execution that don’t fit anywhere else. Some of the most important values you might encounter include:

• Using where: A WHERE condition is being applied to filter rows for this table.
• Using index: MySQL can serve all required columns from the index alone without touching the table data, which is known as a covering index and is generally very efficient.
• Using temporary: MySQL is creating an internal temporary table to hold intermediate results, for example for complex GROUP BY or subquery processing. On large data sets this may severely impact performance.
• Using filesort: MySQL performs a separate sort step, often for ORDER BY or GROUP BY, which may be done in memory or on disk and is usually more expensive than using an index-based order.
• Fulltext-related notes: When using FULLTEXT indexes, EXPLAIN can reveal whether a fulltext search is being correctly combined with joins or other filters.

Pay particular attention to Using temporary and Using filesort in Extra when queries are slow, since both can indicate heavy sorting or grouping work that might be offloaded to better indexes or restructured queries.

EXPLAIN ANALYZE: seeing the real execution costs

Starting with MySQL 8.0.18, you get an even more powerful tool: EXPLAIN ANALYZE, which actually runs the query and attaches runtime statistics to the plan. This bridges the gap between theoretical cost estimates and real-world performance.

Unlike vanilla EXPLAIN, which only predicts, EXPLAIN ANALYZE executes the statement and measures how long each iterator (step) in the plan takes, how many rows it returns, and how many loops it performs. The syntax is straightforward:

Run: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

When you run EXPLAIN ANALYZE, MySQL uses FORMAT=tree automatically and outputs a tree‑shaped plan that combines estimated and actual metrics. For each node you can see:

• Estimated execution cost: The optimizer’s model of how expensive this step should be.
• Estimated rows: How many rows were expected to be returned from this iterator.
• Actual time to first row: How long it took before the first row was produced.
• Actual time spent per loop: Average time in milliseconds to execute the iterator, including its children but not its parent, across loops.
• Actual rows and loops: How many rows were truly returned and how many times the iterator ran.

You can use EXPLAIN ANALYZE with SELECT, multi-table UPDATE, DELETE and TABLE statements. It’s especially useful when the normal EXPLAIN suggests one thing, but the query still behaves differently in production, because now you can compare estimated vs actual row counts and costs.

Large mismatches between estimates and actuals reveal where the optimizer’s statistics are off or where complex predicates (including functions, UDFs or stored routines) make cost prediction inaccurate. Those places are prime targets for schema tweaks, new indexes, or query rewrites.

Classic EXPLAIN vs EXPLAIN ANALYZE: strengths and limitations

Even though EXPLAIN and EXPLAIN ANALYZE are incredibly useful, it’s important to understand their blind spots so you don’t over-interpret the output.

Regular EXPLAIN is essentially an approximation of what the optimizer thinks will happen. Some of its statistics are rough estimates, and certain internal optimizations are not exposed in the output at all. For example, it doesn’t tell you how triggers, stored functions or user-defined functions will affect CPU time. It also does not show what happens inside stored procedures.

Some labels in the output are an oversimplification of what’s really going on. For instance, the type column is called a join type, though it really represents access type. The Extra value Using temporary does not distinguish between in-memory vs on-disk temporary tables, and Using filesort covers both memory and disk-based sorts under the same label.

EXPLAIN ANALYZE solves some of these issues by showing real timing and row counts, but it also has trade-offs. It must execute the query, which may be expensive or even dangerous on production if the query is resource-intensive or modifies data. Also, raw JSON output is not currently available directly for EXPLAIN ANALYZE, so you’ll mostly work with the tree format in the CLI or client tools.

The right approach is to use EXPLAIN for fast, low-impact insight into the plan, and EXPLAIN ANALYZE for deeper investigations where you can afford to actually run the query.

Using EXPLAIN and EXPLAIN ANALYZE to optimize real queries

Reading EXPLAIN is only useful if it leads to better queries; the key is to translate what you see into specific index and SQL changes. Let’s walk through some common optimization scenarios that you can tackle directly with EXPLAIN’s help.

Imagine a simple query on an employees table without any relevant indexes:

Filter example: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

Without indexes, EXPLAIN will likely show type = ALL and a very large value in rows (for example, around 299,000 rows) indicating a full table scan. That’s a clear signal you need an index that supports both conditions.

One naive approach is to create two separate indexes, one on last_name and one on first_name, but that still doesn’t make the combination search efficient. MySQL can quickly find everyone with last_name = ‘Puppo’ or everyone with first_name = ‘Kendra’, but identifying exactly the ‘Kendra Puppo’ row requires intersecting those sets, which is not as efficient as you might hope.

The better solution is a multi-column index that follows your most selective and frequently used search pattern:

Index creation: CREATE INDEX fullnames ON employees(last_name, first_name);

Rerunning EXPLAIN now, you should see that this index is used, type improves to something like ref or even const depending on uniqueness, and rows drops to 1. This confirms that only one row needs to be touched, which explains the massive performance improvement.

A similar pattern appears in join optimization. Suppose you have two tables A and B with a shared column X used for joining, and you initially create them without indexes:

Schema: CREATE TABLE A ( X VARCHAR(10), Y VARCHAR(10));
CREATE TABLE B ( X VARCHAR(10), Z VARCHAR(10));

Running a join with a filter on A.X may show full scans on both A and B, each with around 10,000 rows examined, because the engine has no better option than to brute-force compare rows:

Join query: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

Once you add appropriate indexes and foreign key constraints, EXPLAIN reflects the improved strategy. For example:

Index & FK: CREATE UNIQUE INDEX a_unique_index ON A(X);
CREATE UNIQUE INDEX b_unique_index ON B(X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

After these changes, EXPLAIN will typically show type improving (e.g. eq_ref on the joined table), as well as rows dropping from thousands to around 1, proving that the join now uses efficient index lookups rather than scanning everything.

Pattern matching, sorting and other common pitfalls

EXPLAIN is especially valuable when dealing with patterns, sorts and grouping, where seemingly harmless SQL can completely disable indexes. Recognizing these patterns early saves you a lot of guesswork.

One classic issue is pattern matching with leading wildcards. For example, a query with WHERE email LIKE '%yahoo.com' makes it impossible for a standard index on email to help, because MySQL has no way to jump directly to rows ending in that suffix. EXPLAIN will typically show type = ALL and a large rows count.

Trailing wildcards, like LIKE 'john%', are index-friendly, because the engine can use the index to find the range of rows starting with ‘john’. EXPLAIN will confirm this by showing a good access type and a small rows estimate on the indexed column.

Sorting and grouping are another performance hotspot. If EXPLAIN shows Using filesort in Extra together with a large rows estimate, your ORDER BY or GROUP BY is probably forcing an explicit sort step.

Sort example: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

Adding an index on sale_date allows MySQL to read rows directly in sorted order, which EXPLAIN usually reflects by removing Using filesort and possibly changing the access type to index or better.

At the same time, be careful not to over-index. EXPLAIN can show you which indexes appear in possible_keys but are never used as key by any important query. Those might be candidates for removal, but you must check overall workload first; just because one query ignores an index doesn’t mean other critical queries don’t rely on it.

SHOW WARNINGS and extended explain markers

When an EXPLAINed query does not even parse correctly, you can still extract some insight using SHOW WARNINGS;. This command displays information about the last non-diagnostic statement and can give you partial hints about how MySQL interpreted fragments of your query.

For instance, if you run EXPLAIN on a query that references a non-existent table or contains a syntax error, EXPLAIN might fail, but SHOW WARNINGS can still reveal internal markers indicating how parts of the query would be treated. You might see markers such as:

• <index_lookup>(query fragment): suggests that an index lookup would be performed for that fragment if the query were valid.
• <primary_index_lookup>(query fragment): indicates that a primary key-based lookup would occur.
• <if>(condition, expr1, expr2): points out conditional evaluation in that part of the query.
• <temporary table>: indicates creation of an internal temporary table for intermediate results, for example before a join.

Combining EXPLAIN EXTENDED with SHOW WARNINGS is a powerful way to see not just the plan but also the optimizer’s rewritten query, which sometimes reveals simplifications, predicate pushdown, or other transformations you might not expect.

Beyond EXPLAIN: data model, architecture and tooling

While EXPLAIN is a fantastic microscope for individual queries, sustainable performance also depends on your overall data model, system architecture, and hardware. You can’t fix a fundamentally bad schema or overloaded instance purely by tweaking where clauses.

A good starting point is a data model that separates very different workloads and JSON processing in SQL. For example, mixing high-volume access logs with transactional data in the same MySQL instance is a recipe for long-term pain: as the company grows, logging traffic can saturate I/O and degrade critical business queries. Splitting logs into a dedicated store or data warehouse is often more scalable.

Software architecture also plays a major role. Decisions about monolith vs microservices, data warehouses, and specialized stores (like graph databases for recommendations) will influence what kind of queries you run and where. If a service needs near real-time analytics on huge datasets, a standard OLTP MySQL instance may not be the best target for those queries.

Hardware and instance sizing matter too. Disk I/O, memory, CPU, and network throughput all affect query latency. Identifying peak usage periods and scheduling heavy analytical or administrative queries outside those windows can protect user-facing performance. With managed DBaaS offerings, you can often scale instances or move to more performant classes as needed.

Finally, complement EXPLAIN with continuous monitoring and profiling tools. MySQL’s own performance schema and slow query log, MySQL Workbench, Percona Toolkit’s pt-query-digest, web tools like EverSQL, and platforms like Releem can automatically surface your worst offenders and even suggest optimizations. EXPLAIN is then your surgical tool for those specific queries.

Adding EXPLAIN and EXPLAIN ANALYZE to your toolbox lets you stop guessing about slow queries and start reasoning with concrete evidence. By understanding ids, select types, access methods, index usage, row estimates, filters, and extra flags, you can methodically remove full table scans, tame expensive joins, design smarter indexes, and avoid wasteful sorts and temporary tables, all while keeping an eye on the bigger picture of schema design and infrastructure so that your MySQL workloads stay fast and predictable as they grow.