How to use EXPLAIN ANALYZE to debug slow MySQL queries

Use EXPLAIN ANALYZE to inspect the actual execution time and row counts of your MySQL queries. These steps target MySQL 8.0 and later, where EXPLAIN ANALYZE is supported natively without requiring the slow_query_log.

Prerequisites

• MySQL 8.0.20 or later installed on Linux (Ubuntu 24.04, AlmaLinux 9, or similar).
• Access to a MySQL user with SELECT and PROCESS privileges.
• A database containing tables with at least one query that runs slowly.
• Ensure the global variable performance_schema is enabled (default on modern installs).

Step 1: Enable the required performance schema features

Verify that the performance schema is active and that the events collection is available for query analysis. Run these SQL statements to confirm the status.

SYS:SHOW VARIABLES LIKE 'performance_schema%';
SYS:SELECT * FROM performance_schema.setup_actors
     WHERE actor_name = 'USER' AND actor_type = 'USER';

You should see performance_schema set to ON and the USER actor listed. If either is missing, restart MySQL with performance_schema=ON in the my.cnf file.

Step 2: Prepare a test table and insert data

Create a simple table and populate it with enough rows to trigger a slow query. This ensures you have a realistic environment for testing.

CREATE TABLE test_slow (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    category INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_slow (name, category)
SELECT 'Item ' || n, FLOOR(RANDOM() % 10)
FROM (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t1
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t2
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t3
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t4
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t5
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t6;

This creates 3,125 rows. Run a query that scans the whole table to simulate a slow operation.

Step 3: Run a slow query without EXPLAIN ANALYZE

Execute a query that filters on a column without an index. This will take noticeable time if the table is large enough.

SELECT * FROM test_slow WHERE category = 5;

Notice the execution time in the client output. It might take a few milliseconds to seconds depending on your hardware.

Step 4: Run EXPLAIN ANALYZE on the slow query

Replace the standard SELECT with EXPLAIN ANALYZE to get detailed timing and row count data.

EXPLAIN ANALYZE
SELECT * FROM test_slow WHERE category = 5;

The output will show the actual time taken, rows examined, and whether an index was used. Look for the rows_examined column. A high number indicates a full table scan.

Step 5: Add an index to fix the slow query

Create an index on the category column to force MySQL to use an index scan instead of a full scan.

ALTER TABLE test_slow ADD INDEX idx_category (category);

Run the same EXPLAIN ANALYZE query again to confirm the improvement.

EXPLAIN ANALYZE
SELECT * FROM test_slow WHERE category = 5;

You should now see type: range or type: ref and a much lower rows_examined count.

Step 6: Analyze a JOIN query for performance issues

Test a JOIN between two tables to see how MySQL chooses join order and indexes.

CREATE TABLE test_slow2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category INT,
    value DECIMAL(10,2)
);

INSERT INTO test_slow2 (category, value)
SELECT category, RAND() * 100
FROM test_slow;

Run a JOIN that previously scanned both tables fully.

EXPLAIN ANALYZE
SELECT t1.name, t2.value
FROM test_slow t1
JOIN test_slow2 t2 ON t1.category = t2.category
WHERE t1.category = 5;

Check the rows_examined for both tables. If one shows a high number, add an index on category in test_slow2.

Verify the installation

Run the final EXPLAIN ANALYZE on the optimized query to confirm the fix. The output should show low execution time and correct index usage.

EXPLAIN ANALYZE
SELECT * FROM test_slow WHERE category = 5;

Expected output includes rows_examined: 1 and Extra: Using index condition. If you still see a full scan, check that the index is not dropped or that the query optimizer is not ignoring it.

Troubleshooting

If EXPLAIN ANALYZE still shows a full table scan, verify that the index is actually present.

SHOW INDEX FROM test_slow;

Ensure the index name matches what you expect. If the optimizer chooses a full scan despite an index, the query may involve functions on the column that prevent index usage.

-- Bad: function on column prevents index use
EXPLAIN ANALYZE
SELECT * FROM test_slow WHERE YEAR(created_at) = 2024;

Fix this by rewriting the query to compare the raw column.

EXPLAIN ANALYZE
SELECT * FROM test_slow WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Also, check for temporary tables or filesort in the Extra column. If you see Using temporary or Using filesort, consider adding an index or rewriting the query to avoid sorting.

EXPLAIN ANALYZE
SELECT * FROM test_slow ORDER BY name;

Add an index on name to eliminate the filesort.

ALTER TABLE test_slow ADD INDEX idx_name (name);

Re-run the query to confirm the Extra column no longer shows Using filesort.