MySQL EXPLAIN: The Ultimate Performance Guide

MySQL EXPLAIN — The Ultimate Performance Guide.md

🚀 MySQL EXPLAIN: The Ultimate Performance Guide

Database optimization can feel like a dark art, but the EXPLAIN command is your "Matrix vision." It reveals how MySQL plans to execute your query before you run it.

---

1. The Starting Line: Slow Query Log

You can't fix what you can't find. Use the Slow Query Log to catch queries that exceed a specific time threshold.

-- Check current settings
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- Set threshold to 0.5 seconds (Global)
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = 'ON';

---

2. Reading the EXPLAIN Table

Focus on these five critical columns. They tell the story of your query's efficiency.

Column	Importance	High-Level Meaning
type	⭐⭐⭐	How MySQL finds the rows (The "Speed Limit").
possible_keys	⭐⭐	The Candidates: Every index MySQL could have used.
key	⭐⭐⭐	The Winner: The actual index MySQL decided to use.
rows	⭐⭐	Estimate of how many rows MySQL must scan.
Extra	⭐⭐⭐	Critical "under-the-hood" details (Sorts, Temps, Loops).

💡 Candidates (possible_keys) vs. The Winner (key)

Think of this as the optimizer's narrowing-down process:

• possible_keys: These are the "Shortlisted" indexes. If this is NULL, you have no indexes that match your WHERE or JOIN clauses.
• key: This is the final choice. MySQL picks the index with the "lowest cost" (usually the one that eliminates the most rows the fastest).

Why would they differ? > If possible_keys shows an index but key is NULL, MySQL decided it’s actually faster to scan the whole table (type: ALL) than to use the index—this often happens on very small tables or if you are fetching >20% of the total rows.

---

3. The table Column: Decoding Special Names

If the table column looks like a weird tag, MySQL is working with internal data:

• <derivedN>: A temporary table generated from a Subquery in your FROM clause. The N refers to the id line in the output where that subquery was defined.
• <unionM,N>: The internal result of a UNION. MySQL is merging results from lines M and N and performing deduplication.
• <subqueryN>: A subquery that has been optimized into a specialized lookup table for efficiency.

---

4. The type Hierarchy (Fastest to Slowest)

Your goal is to move up this list.

🏎️ The "Fast" Lane

• system / const: MySQL finds exactly one row instantly. (Searching by Primary Key).
• eq_ref: The "Golden Ticket" for joins. A 1-to-1 match using a Unique/Primary key.
• ref: Using a standard index to find multiple rows.
  • Analogy: Finding all "Smiths" in a phone book.

🚲 The "Proceed with Caution" Lane

• range: Using an index to find a slice of data (BETWEEN, IN(), >, <).
• index: The Full Index Scan. MySQL reads the entire index file. Better than a table scan, but still a lot of reading.

🐌 The "Danger" Zone

• ALL: The Full Table Scan. MySQL reads every single byte of the table from disk.
  • Fix: Add an index or refine your WHERE clause.

Tip

When is ALL okay?
If the table column shows <derivedN> or <unionM,N> and the "rows" count is very small (e.g., under 100), an ALL scan is perfectly fine. It just means MySQL is reading a small temporary result set it just created in memory.

---

5. The Extra Column: Red Flags & Green Lights

This column reveals the "extra effort" MySQL has to put in.

Flag	Impact	Meaning
Using index	✅ Good	Covering Index: MySQL got all data from the index itself without touching table rows.
Using index condition	✅ Good	Index Condition Pushdown (ICP): MySQL filtered rows at the storage engine level using the index before even reading the full table rows. (Saves I/O).
Using index for group-by	✅ Good	"Loose Index Scan." MySQL jumped between unique index values (very fast).
Using where	⚠️ Neutral	MySQL filters rows after fetching them. Watch out if type is ALL.
Using intersect(...)	⚠️ Neutral	Index Merge: MySQL is using two separate indexes and finding the overlap. Better than a scan, but usually slower than a single Composite Index.
Using filesort	🚨 Bad	MySQL had to manually sort data because the index order didn't match your ORDER BY.
Using temporary	🚨 Bad	An internal table was built in memory to process the result (Common with UNION or DISTINCT).
Dependent Subquery	💀 Very Bad	The subquery runs once for every row in the outer query. This is a performance killer.

Note

ICP Explanation:
Normally, the process of fetching data has two steps:

1. The Storage Engine (e.g., InnoDB) uses an index to find a row and hands the entire row back to the MySQL Server.
2. The MySQL Server then looks at the row and says, "Does this actually match the rest of my WHERE clause?" Using index condition means MySQL "pushed" the filtering logic down to the Storage Engine. Instead of handing back every row that might match, the Storage Engine checks the conditions against the index fields first and only hands back the rows that actually match.

Example: You have an index on (last_name, birth_year).
Your query is: WHERE last_name LIKE 'Smi%' AND birth_year > 1990.\

Without ICP, MySQL finds all "Smi%" entries and reads the full row for every single one from the disk just to check the year. With Using index condition, MySQL checks the birth_year while it's still looking at the index. It only goes to the disk for the "Smi" people actually born after 1990.

---

6. Common "Index Killers"

Even with an index, these mistakes force a slow type: ALL scan:

1. Leading Wildcards: LIKE '%smith' (Slow). LIKE 'smith%' (Fast).
2. Function Wrappers: WHERE YEAR(date) = 2026 (Slow). Use WHERE date >= '2026-01-01' (Fast).
3. Type Mismatch: Searching a VARCHAR column with a number (e.g., WHERE string_id = 123). MySQL has to convert every row to a number to compare them.

---

7. Pro-Tip: The Composite Index Rule

If you frequently query WHERE status = 'active' AND created_at > '...', a single index on (status, created_at) is vastly superior to two separate indexes.

• The Leftmost Prefix Rule: If you have an index on (A, B, C), it can be used for queries on (A), (A, B), or (A, B, C). It cannot be used for a query only on (B) or (C).