Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
When a query is slow, your first move is EXPLAIN. It shows you exactly how MySQL plans to execute the query — which indexes it uses, how many rows it expects to scan, and where it’s doing expensive work. Once you can read it, you can fix almost any slow query.

​
Running EXPLAIN

Put EXPLAIN in front of any SELECT: 
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
MySQL returns one row per table in the query. For joins, you get multiple rows — one per table involved.

​
The Columns That Matter

A full EXPLAIN row looks like this: 
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |   12 |   100.00 | NULL        |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
Most columns matter some of the time. These matter almost all of the time:

​
type — How MySQL scans the table

This is the most important column. It tells you the access method, from fastest to slowest:
typeMeaningWhen you see it
constSingle row by primary key or unique indexWHERE id = 5
eq_refOne row per row from previous tableJOIN on primary key
refMultiple rows from a non-unique indexWHERE user_id = 42
rangeIndex range scanBETWEEN, >, <, IN
indexFull index scanScanning index left-to-right
ALLFull table scanNo usable index
ALL is the one to fix. It means MySQL is reading every row in the table. On a table with a million rows, that’s a million reads for every query execution.

​
key — Which index MySQL chose

NULL means no index was used. If possible_keys lists indexes but key is NULL, MySQL decided a full scan was cheaper — usually because the table is small or the query isn’t selective enough.

​
rows — Estimated rows examined

An estimate, not a guarantee. MySQL uses statistics to produce this number. A query with rows: 1 is fast; rows: 5000000 on a production table is a problem.

​
Extra — Additional execution details

The Extra column holds important flags:
Extra valueMeaning
Using indexCovering index — no row lookups needed (fast)
Using whereMySQL filters rows after reading them
Using filesortSort can’t use an index — extra sort step
Using temporaryMySQL created a temp table (expensive on large sets)
Using index conditionIndex Condition Pushdown — filtering at the storage layer
Using filesort and Using temporary together on large tables are red flags.

​
A Worked Example

CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    status     VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL
);

-- No indexes yet
EXPLAIN SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at;

| type | key  | rows    | Extra                          |
|------|------|---------|--------------------------------|
| ALL  | NULL | 1000000 | Using where; Using filesort    |
Two problems: full table scan (ALL) and a filesort. Fix both with a composite index (see How MySQL Indexes Work for index design principles): 
CREATE INDEX idx_status_created ON orders (status, created_at);

EXPLAIN SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at;

| type | key                | rows | Extra |
|------|--------------------|------|-------|
| ref  | idx_status_created | 4200 | NULL  |
rows dropped from 1,000,000 to 4,200. type is now ref — an equality lookup on status. The filesort is gone because the index already orders rows by created_at within each status value.

​
EXPLAIN ANALYZE

MySQL 8.0 added EXPLAIN ANALYZE, which actually executes the query and returns real timing alongside the estimates: 
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-> Index lookup on orders using idx_user_id (user_id=42)
   (cost=4.50 rows=12) (actual time=0.045..0.112 rows=11 loops=1)
cost and rows are the optimizer’s estimates. actual time and actual rows are what really happened. A large gap between estimated and actual rows means stale statistics — run ANALYZE TABLE orders to update them.

​
Frequently Asked Questions

​
What’s the relationship between EXPLAIN and covering indexes?

Extra: Using index means MySQL is using a covering index — it can answer the query entirely from the index without reading table rows. That’s one of the most impactful optimizations available on read-heavy tables. See Covering Indexes in MySQL for how to design them.

​
My query has type: index — is that okay?

Not really. type: index means MySQL scanned the entire index (instead of the table), which is faster than ALL but still a full scan. It appears when a query needs all rows but can satisfy it from the index. If you’re hitting this on large tables, check whether a covering index or a WHERE clause could narrow the scan.

​
Why does MySQL ignore the index I created?

The optimizer chooses indexes based on statistics. If the table is small, MySQL may decide a full scan is cheaper. If the statistics are stale, it may underestimate how selective your index is. Run ANALYZE TABLE to refresh statistics, then re-check EXPLAIN.

​
Can I use EXPLAIN on INSERT, UPDATE, and DELETE?

Yes — EXPLAIN works on all DML statements in MySQL 8.0+. The output shows the access method for the rows being modified, which is useful when an UPDATE or DELETE is slow. If you need to identify which queries to EXPLAIN first, the MySQL Slow Query Log captures the slowest queries on your server and ranks them by total time.

​
Troubleshooting

ProblemSolution
type: ALL on a large tableAdd an index on the filtered column
Extra: Using filesortAdd an index that covers both the WHERE and ORDER BY columns
Extra: Using temporaryRewrite the query to avoid GROUP BY on non-indexed columns, or add an index
key: NULL despite an index existingRun ANALYZE TABLE to update statistics; check if the index matches the query’s column order
Estimated rows far from actualRun ANALYZE TABLE to refresh statistics