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.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">localhost:ytt>desc <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">select</span>  r1 <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">from</span> t1 <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">where</span> <span style="font-family: ptima-Regular; color: #d19a66; line-height: 26px">1</span> <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">order</span> <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">by</span> r1\G<br/>*************************** <span style="color: #d19a66; line-height: 26px">1.</span> <span style="color: #c678dd; line-height: 26px">row</span> ***************************<br/>           <span style="color: #c678dd; line-height: 26px">id</span>: <span style="color: #d19a66; line-height: 26px">1</span><br/>  select_type: SIMPLE<br/>        <span style="color: #c678dd; line-height: 26px">table</span>: t1<br/>   <span style="color: #c678dd; line-height: 26px">partitions</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">type</span>: <span style="color: #c678dd; line-height: 26px">index</span><br/>possible_keys: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>          <span style="color: #c678dd; line-height: 26px">key</span>: idx_r1<br/>      key_len: <span style="color: #d19a66; line-height: 26px">5</span><br/>          <span style="color: #c678dd; line-height: 26px">ref</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">rows</span>: <span style="color: #d19a66; line-height: 26px">101745</span><br/>     filtered: <span style="color: #d19a66; line-height: 26px">100.00</span><br/>        Extra: <span style="color: #c678dd; line-height: 26px">Using</span> <span style="color: #c678dd; line-height: 26px">index</span><br/><span style="color: #d19a66; line-height: 26px">1</span> <span style="color: #c678dd; line-height: 26px">row</span> <span style="color: #c678dd; line-height: 26px">in</span> <span style="color: #c678dd; line-height: 26px">set</span>, <span style="color: #d19a66; line-height: 26px">1</span> <span style="color: #c678dd; line-height: 26px">warning</span> (<span style="color: #d19a66; line-height: 26px">0.00</span> sec)</code>

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.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">localhost:ytt>desc <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">select</span>  r1 <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">from</span> t1 <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">where</span> <span style="font-family: ptima-Regular; color: #d19a66; line-height: 26px">1</span> <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">order</span> <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">by</span> r1 <span style="font-family: ptima-Regular; color: #c678dd; line-height: 26px">desc</span>\G<br/>*************************** <span style="color: #d19a66; line-height: 26px">1.</span> <span style="color: #c678dd; line-height: 26px">row</span> ***************************<br/>           <span style="color: #c678dd; line-height: 26px">id</span>: <span style="color: #d19a66; line-height: 26px">1</span><br/>  select_type: SIMPLE<br/>        <span style="color: #c678dd; line-height: 26px">table</span>: t1<br/>   <span style="color: #c678dd; line-height: 26px">partitions</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">type</span>: <span style="color: #c678dd; line-height: 26px">index</span><br/>possible_keys: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>          <span style="color: #c678dd; line-height: 26px">key</span>: idx_r1<br/>      key_len: <span style="color: #d19a66; line-height: 26px">5</span><br/>         <span style="color: #c678dd; line-height: 26px">ref</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">rows</span>: <span style="color: #d19a66; line-height: 26px">101745</span><br/>     filtered: <span style="color: #d19a66; line-height: 26px">100.00</span><br/>        Extra: Backward <span style="color: #c678dd; line-height: 26px">index</span> <span style="color: #c678dd; line-height: 26px">scan</span>; Using index<br/>1 row in <span style="color: #c678dd; line-height: 26px">set</span>, <span style="color: #d19a66; line-height: 26px">1</span> <span style="color: #c678dd; line-height: 26px">warning</span> (<span style="color: #d19a66; line-height: 26px">0.00</span> sec)</code>

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.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">localhost:ytt><span style="color: #c678dd; line-height: 26px">alter</span> <span style="color: #c678dd; line-height: 26px">table</span> t1 <span style="color: #c678dd; line-height: 26px">add</span> <span style="color: #c678dd; line-height: 26px">key</span> idx_r1_desc(r1 <span style="color: #c678dd; line-height: 26px">desc</span>);<br/>Query OK, 0 rows affected (1.68 sec)<br/>Records: 0  Duplicates: 0  Warnings: 0</code>

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.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">localhost:ytt>desc <span style="color: #c678dd; line-height: 26px">select</span> <span style="color: #c678dd; line-height: 26px">min</span>(r2),<span style="color: #c678dd; line-height: 26px">max</span>(r3) <span style="color: #c678dd; line-height: 26px">from</span> t1 \G<br/>*************************** <span style="color: #d19a66; line-height: 26px">1.</span> <span style="color: #c678dd; line-height: 26px">row</span> ***************************<br/>           <span style="color: #c678dd; line-height: 26px">id</span>: <span style="color: #d19a66; line-height: 26px">1</span><br/>  select_type: SIMPLE<br/>        <span style="color: #c678dd; line-height: 26px">table</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>   <span style="color: #c678dd; line-height: 26px">partitions</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">type</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>possible_keys: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>          <span style="color: #c678dd; line-height: 26px">key</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>     key_len: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">ref</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">rows</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>     filtered: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>        Extra: <span style="color: #c678dd; line-height: 26px">Select</span> <span style="color: #c678dd; line-height: 26px">tables</span> optimized away</code>

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.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menad, monospace; font-size: 12px">localhost:ytt><span style="color: #c678dd; line-height: 26px">alter</span> <span style="color: #c678dd; line-height: 26px">table</span> t1 <span style="color: #c678dd; line-height: 26px">add</span> <span style="color: #c678dd; line-height: 26px">key</span> idx_r2(r2), <span style="color: #c678dd; line-height: 26px">add</span> <span style="color: #c678dd; line-height: 26px">key</span> idx_r3(r3);<br/>Query OK, 0 rows affected (2.86 sec)<br/>Records: 0  Duplicates: 0  Warnings: 0</code>

4 Zero limit

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

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px">localhost:ytt>desc <span style="color: #c678dd; line-height: 26px">select</span>  r1 <span style="color: #c678dd; line-height: 26px">from</span> t1 <span style="color: #c678dd; line-height: 26px">where</span> <span style="color: #d19a66; line-height: 26px">1</span> <span style="color: #c678dd; line-height: 26px">order</span> <span style="color: #c678dd; line-height: 26px">by</span> r1 <span style="color: #c678dd; line-height: 26px">limit</span> <span style="color: #d19a66; line-height: 26px">0</span>\G<br/>*************************** <span style="color: #d19a66; line-height: 26px">1.</span> <span style="color: #c678dd; line-height: 26px">row</span> ***************************<br/>           <span style="color: #c678dd; line-height: 26px">id</span>: <span style="color: #d19a66; line-height: 26px">1</span><br/>  select_type: SIMPLE<br/>        <span style="color: #c678dd; line-height: 26px">table</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>   <span style="color: #c678dd; line-height: 26px">partitions</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">type</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>possible_keys: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>          <span style="color: #c678dd; line-height: 26px">key</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>     key_len: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">ref</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>         <span style="color: #c678dd; line-height: 26px">rows</span>: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>     filtered: <span style="color: #56b6c2; line-height: 26px">NULL</span><br/>        Extra: Zero <span style="color: #c678dd; line-height: 26px">limit</span></code>

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.