Slow queries kill application performance. In this tutorial, I will show you how to use MySQL EXPLAIN to diagnose why a query is slow, read the execution plan, identify missing or unused indexes, add the right index, and verify the performance improvement. By the end, you will know how to systematically optimize any slow SELECT query on a LAMP stack.
Prerequisites
- A LAMP server with MySQL 5.7 or later
- Access to the MySQL command-line client or phpMyAdmin
- A database with at least one table containing a few thousand rows
- Basic familiarity with SQL SELECT statements
Step 1 - Identify a Slow Query
First, I need a query that is actually slow. If you already have one from your application logs or slow query log, use that. If not, I will create a sample scenario.
Connect to MySQL:
mysql -u root -p
Create a test database and table with sample data:
CREATE DATABASE shop; USE shop;
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
Insert sample data to simulate a production table:
INSERT INTO orders (user_id, product_id, order_date, amount) SELECT FLOOR(1 + RAND() * 10000), FLOOR(1 + RAND() * 500), DATE_ADD('2022-01-01', INTERVAL FLOOR(RAND() * 730) DAY), ROUND(10 + RAND() * 490, 2) FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t5, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t6 LIMIT 50000;
This creates 50,000 sample orders. Now run a query that will be slow without an index:
SELECT * FROM orders WHERE user_id = 1234 ORDER BY order_date DESC;
This query might feel fast on 50k rows, but on millions it would crawl. I will use EXPLAIN to see why.
Step 2 - Run EXPLAIN on the Query
Prefix your SELECT with EXPLAIN to see the execution plan:
EXPLAIN SELECT * FROM orders WHERE user_id = 1234 ORDER BY order_date DESC;
MySQL returns a table with columns describing how it will execute the query. The output shows columns like id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra.
This output tells me exactly what is wrong. I will decode it in the next step.
Step 3 - Read the EXPLAIN Output
The most important columns are:
- type: The join type. ALL means a full table scan (reads every row). This is the slowest type. Better values are index, range, ref, or const.
- possible_keys: Indexes MySQL could use. NULL means no indexes are available for this query.
- key: The index MySQL actually chose. NULL means no index was used.
- rows: Estimated number of rows MySQL must examine. Higher is slower. A full scan means all rows.
- Extra: Additional info. Using filesort means MySQL must sort the results in memory or on disk (slow). Using where means a WHERE clause is applied after reading rows.
In the typical bad case, type is ALL, key is NULL, and Extra shows Using filesort. This query is doing a full table scan and then sorting all rows in memory. On a table with millions of rows, this would take seconds or minutes.
Step 4 - Identify the Missing Index
The query filters on user_id and sorts on order_date. I need an index that supports both operations. A composite index on user_id and order_date will allow MySQL to use the index to find all rows where user_id equals 1234 without a table scan, and return those rows already sorted by order_date without a filesort operation.
The order matters. Put the equality condition column first, then the sort column. Create the index:
CREATE INDEX idx_user_date ON orders (user_id, order_date);
This builds a B-tree index on the combination of user_id and order_date. On 50,000 rows, this takes a few seconds. On larger tables, it may take minutes, so run index creation during low-traffic periods.
Step 5 - Re-run EXPLAIN and Compare
Run EXPLAIN again on the same query:
EXPLAIN SELECT * FROM orders WHERE user_id = 1234 ORDER BY order_date DESC;
Now the output will show improvements. The type changed from ALL to ref, which means MySQL now uses the index for lookups. The key column shows idx_user_date, confirming the index is being used. The rows dropped from 50,000 to a small number, meaning MySQL now examines only the matching rows. The Extra column no longer shows filesort, because the index returns rows already sorted by order_date.
This query went from scanning all rows and sorting in memory to scanning just matching rows with no sort. On a production table, this is the difference between a 10-second query and a 10-millisecond query.
Step 6 - Benchmark the Actual Query Performance
EXPLAIN shows the plan, but I want to measure real execution time. Enable query timing in MySQL:
SET profiling = 1;
Run the query twice: once to warm the cache, then once to measure:
SELECT * FROM orders WHERE user_id = 1234 ORDER BY order_date DESC; SELECT * FROM orders WHERE user_id = 1234 ORDER BY order_date DESC;
Now check the profiles:
SHOW PROFILES;
You will see Query_ID, Duration, and Query columns showing the execution time for each query. The second run with the index should be significantly faster. Before the index, this query would have taken tens or hundreds of milliseconds on this dataset, and much longer on millions of rows.
Step 7 - Understand When NOT to Add an Index
Indexes are not free. They consume disk space and slow down INSERT, UPDATE, and DELETE operations because MySQL must update the index on every write. Add indexes only when the query runs frequently, the table is large (thousands of rows or more), and the query filters or sorts on columns that change rarely.
For rarely-run queries on small tables, a table scan may be faster than maintaining an index. Use EXPLAIN on your actual workload and measure before and after.
Gotcha: Adding too many indexes can slow down writes more than it speeds up reads. Profile your full workload, not just SELECT queries.
Wrap-up
You now know how to use EXPLAIN to diagnose slow queries, interpret execution plans, identify missing indexes, and verify performance improvements. The workflow is: run EXPLAIN, look for type equals ALL or Using filesort in Extra, add a composite index on the filter and sort columns in the right order, re-run EXPLAIN to confirm the index is used, and benchmark the real query time. This same process applies to any slow SELECT on a LAMP stack. Keep your indexes focused on high-frequency queries, and always measure before and after to confirm the win.
