Understanding the Extra Column in MySQL EXPLAIN Output

After previous articles on MySQL execution plans, this article focuses on the Extra column in the EXPLAIN output, explaining its common values and how to use them for SQL optimization.

1 Using index

This hint indicates that the query can be satisfied by scanning the index alone without accessing the table rows, typically because the needed columns are covered by the index.

localhost:ytt>desc
select
r1
from
t1
where
1
order
by
r1\G
***************************
1.
row
***************************
id
:
1
select_type: SIMPLE
table
: t1
partitions
:
NULL
type
:
index
possible_keys:
NULL
key
: idx_r1
key_len:
5
ref
:
NULL
rows
:
101745
filtered:
100.00
Extra:
Using
index
1
row
in
set
,
1
warning
(
0.00
sec)

The query reads column r1 from table t1 ; because r1 is indexed, the Extra column shows Using index . This explains why many coding guidelines discourage SELECT * and recommend selecting only needed indexed columns.

2 Backward index scan

This hint means the index is used but scanned in reverse order, which occurs when the query requires descending order.

localhost:ytt>desc
select
r1
from
t1
where
1
order
by
r1
desc
\G
***************************
1.
row
***************************
id
:
1
select_type: SIMPLE
table
: t1
partitions
:
NULL
type
:
index
possible_keys:
NULL
key
: idx_r1
key_len:
5
ref
:
NULL
rows
:
101745
filtered:
100.00
Extra: Backward
index
scan
; Using index
1 row in
set
,
1
warning
(
0.00
sec)

The query orders r1 descending, so MySQL scans the index backward. Creating a dedicated descending index (e.g., ALTER TABLE t1 ADD KEY idx_r1_desc(r1 DESC); ) can make the plan clearer.

localhost:ytt>
alter
table
t1
add
key
idx_r1_desc(r1
desc
);
Query OK, 0 rows affected (1.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

3 Select tables optimized away

This hint means the optimizer can compute the result without reading any table rows, usually because the query depends only on constant expressions or can be resolved from indexes.

localhost:ytt>desc
select
min
(r2),
max
(r3)
from
t1 \G
***************************
1.
row
***************************
id
:
1
select_type: SIMPLE
table
:
NULL
partitions
:
NULL
type
:
NULL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref
:
NULL
rows
:
NULL
filtered:
NULL
Extra:
Select
tables
optimized away

Adding indexes on r2 and r3 (e.g., ALTER TABLE t1 ADD KEY idx_r2(r2), ADD KEY idx_r3(r3); ) changes the plan so the Extra column no longer shows NULL but the query can still be resolved without a full table scan.

localhost:ytt>
alter
table
t1
add
key
idx_r2(r2),
add
key
idx_r3(r3);
Query OK, 0 rows affected (2.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

4 Zero limit

This hint appears when the query contains LIMIT 0 . It is useful for checking statement validity without actually retrieving rows.

localhost:ytt>desc
select
r1
from
t1
where
1
order
by
r1
limit
0
\G
***************************
1.
row
***************************
id
:
1
select_type: SIMPLE
table
:
NULL
partitions
:
NULL
type
:
NULL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref
:
NULL
rows
:
NULL
filtered:
NULL
Extra: Zero
limit

Even without LIMIT 0 , MySQL will still parse the statement and report any syntax errors; adding the clause is mainly a business‑level way to explicitly indicate that no rows are needed.

The article concludes here and invites readers to follow future posts for more MySQL optimization tips.