Read MySQL EXPLAIN Output to Fix Slow Queries (2026)

The most surprising thing about EXPLAIN is that it doesn’t actually run your query; it just analyzes how it would run.

Let’s see how EXPLAIN helps us diagnose a slow query. Imagine this table:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 1200.00),
('Keyboard', 'Electronics', 75.00),
('Mouse', 'Electronics', 25.00),
('Desk Chair', 'Furniture', 150.00),
('Monitor', 'Electronics', 300.00);

Now, a query that might be slow on a large table:

SELECT * FROM products WHERE category = 'Electronics';

Run EXPLAIN before it:

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

The output will look something like this:

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where              |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

This is the blueprint for how MySQL plans to execute your query. Let’s break down the key parts and how they help us.

The type column is crucial. Here, ALL means MySQL is performing a full table scan. It has to look at every single row in the products table to find the ones where category is 'Electronics'. For small tables, this is fine. For tables with millions of rows, this is a guaranteed performance killer.

possible_keys shows which indexes MySQL could use, and key shows which one it actually chose. Here, both are NULL, indicating no index was considered or used for filtering on category.

The rows column estimates how many rows MySQL expects to examine. 5 for this small table is accurate, but on a large table, this number would be huge.

The filtered column (in newer MySQL versions) shows the percentage of rows that MySQL expects to be filtered out by the WHERE clause. 20.00 means it expects to examine 20% of the rows after the scan, which is odd when type is ALL. This is because it’s estimating based on the WHERE clause after the scan.

The Extra column provides additional context. Using where means the WHERE clause is being used to filter rows after they’ve been retrieved. This is typical for a full table scan.

Now, let’s fix this. The problem is the lack of an index on the category column. We can create one:

CREATE INDEX idx_category ON products (category);

After creating the index, run EXPLAIN again:

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

The output changes dramatically:

+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_category  | idx_category | 52      | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+

See the difference?

• type is now ref. This is much better than ALL. It means MySQL can use an index to directly look up rows matching a specific value in category.
• possible_keys and key both show idx_category. MySQL is using our new index.
• ref shows const. This means the index is being used to match a constant value ('Electronics').
• rows has dropped from 5 (or potentially millions) to 2. MySQL now estimates it only needs to examine 2 rows.
• Extra is NULL (or might show Using index condition in some cases), which is generally good. It means the filtering is happening efficiently as part of the index lookup.

The fix worked because an index on category allows MySQL to quickly locate the relevant rows without scanning the entire table. Instead of reading every row, it uses the index structure (like a sorted phone book) to jump directly to the entries for 'Electronics'.

What if we also wanted to sort by price?

EXPLAIN SELECT * FROM products WHERE category = 'Electronics' ORDER BY price;

Output:

+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_category  | idx_category | 52      | const |    2 |   100.00 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+--------------------------+

Notice Using filesort in Extra. This means MySQL found the rows efficiently but then had to sort them in memory or on disk because there wasn’t a suitable index to provide the order. To fix this, we’d add price to the index, making it a composite index:

-- Drop the old index first if it exists
DROP INDEX idx_category ON products;
-- Create a composite index
CREATE INDEX idx_category_price ON products (category, price);

Running EXPLAIN again:

EXPLAIN SELECT * FROM products WHERE category = 'Electronics' ORDER BY price;

Output:

+----+-------------+----------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+----------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | products | NULL       | ref  | idx_category_price   | idx_category_price   | 52      | const |    2 |   100.00 | Using index condition; Using index |
+----+-------------+----------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+

Now, Using filesort is gone. The Extra column might show Using index or Using index condition. This means the index idx_category_price is used for both filtering by category and providing the rows already sorted by price. The order of columns in a composite index (category, price) matters significantly for query optimization.

A common pitfall is misinterpreting rows. It’s an estimate. If the estimate is wildly off, it might suggest outdated table statistics. Running ANALYZE TABLE products; can help refresh these statistics, potentially leading EXPLAIN to a better plan.

The next common problem you’ll encounter is Using temporary in the Extra column, which indicates MySQL is creating a temporary table to process the query, often due to complex joins, GROUP BY, or DISTINCT operations that can’t be satisfied by indexes alone.