Using EXPLAIN to Analyze and Optimize a Simple MySQL Query

The column series "SQL Tuning" resumes after 22 months, targeting MySQL DBAs, developers, and users migrating to MySQL, and aims to dissect table design and SQL writing, which together account for most performance issues.

When a slow SQL is identified, three common investigation methods are: personal experience rewriting, adhering to company coding standards, and examining the execution plan with EXPLAIN to check index usage, join order, and algorithms.

EXPLAIN (also known as DESC or DESCRIBE) reveals whether a query matches an index, uses temporary tables, requires extra sorting, and can output results in table, JSON, or tree formats. The key fields include possible_keys (candidate indexes), key (chosen index), rows (estimated rows examined), Extra (additional information), and warnings (final parsed SQL).

Example of a simple query and its EXPLAIN output:

debian-ytt1:ytt>desc  select * from t1 where r1 = 4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_r1,idx_u1
key: idx_u1
key_len: 5
ref: const
rows: 29324
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)

The output shows that the optimizer chose the composite index idx_u1 , but still had to scan about 29,000 rows because the index selectivity is low (cardinality of r1 is only 5).

To illustrate further optimization, a new composite index (r1, id DESC) is added to eliminate the filesort caused by an ORDER BY clause:

debian-ytt1:ytt>alter table t1 add key idx_r1_id_desc (r1,id desc);
Query OK, 0 rows affected (1.06 sec)

Running EXPLAIN again still shows Using filesort because the optimizer still prefers idx_u1 :

debian-ytt1:ytt>desc  select * from t1 where r1 = 4 order by id desc\G
...
Extra: Using filesort

Forcing the new index demonstrates a higher row count (32,176) but removes the filesort:

debian-ytt1:ytt>desc  select * from t1 force index(idx_r1_id_desc) where r1 = 4 order by id desc\G
...
Extra: NULL

Using EXPLAIN ANALYZE to compare costs shows that the original plan (with filesort) has a lower estimated cost (3149.15) than the forced‑index plan (3434.35), confirming that the added index does not provide a performance benefit and can be dropped.

The article concludes that while EXPLAIN is essential for diagnosing query performance, true optimization may require revisiting schema design, adjusting business logic, or reducing query frequency when index selectivity is poor.