Understanding MySQL EXPLAIN EXTRA Hints: Impossible, No Matching, Recursive, Rematerialize, and Canned N Databases

Author Yang Taotao, a technical expert at ActionOpenSource, presents an in‑depth guide on interpreting MySQL EXPLAIN EXTRA hints and optimizing queries based on those hints.

1. Impossible series

Impossible WHERE

Indicates that the WHERE condition can never be true, so the query will return no rows.

localhost:ytt>desc select * from t1 where null=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: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

Impossible HAVING

Indicates that the HAVING condition can never be true after GROUP BY filtering.

localhost:ytt>desc select r1 from t1 group by r1 having false\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: NULL
  ...
  Extra: Impossible HAVING
1 row in set, 1 warning (0.01 sec)

Impossible WHERE noticed after reading const tables

Occurs when a constant table is read and further filtering makes the result impossible.

localhost:ytt>desc select * from t1 where f1=6 and f0=110 and r4=900\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: NULL
  ...
  Extra: Impossible WHERE noticed after reading const tables
1 row in set, 1 warning (0.00 sec)

2. No matching/No table series

No matching min/max row

Shows that there is no matching minimum or maximum record.

localhost:ytt>desc select min(r1) from t2 where 1\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: t2
  type: ALL
  ...
  Extra: No matching min/max row
1 row in set, 1 warning (0.01 sec)

No matching row in const table

Indicates that a constant table (or primary‑key lookup) yields no rows.

localhost:ytt>desc select * from t1 where f1=6 and f0=112\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: NULL
  ...
  Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

No tables used

Means the query uses only constants and does not access any tables.

localhost:ytt>desc select now()\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: NULL
  ...
  Extra: No tables used
1 row in set, 1 warning (0.00 sec)

3. Recursive

Indicates that the query contains a recursive common table expression; the depth of recursion affects resource usage.

localhost:ytt>desc with recursive tmp(a) as (select 1 union all select a+1 from tmp where a<10) table tmp\G
*************************** 3. row ***************************
  id: 3
  select_type: UNION
  table: tmp
  type: ALL
  rows: 2
  filtered: 50.00
  Extra: Recursive; Using where
3 rows in set, 1 warning (0.01 sec)

4. Rematerialize

Occurs when a lateral derived table is re‑materialized for each outer row, often because derived_merge=off is set, leading to poor performance.

localhost:ytt>desc select a.* from t1 a, lateral (select * from t1 where r4=a.r4) b\G
*************************** 1. row ***************************
  id: 1
  select_type: PRIMARY
  table: a
  type: ALL
  rows: 101700
  Extra: Rematerialize (
)
... (additional rows omitted for brevity)

Enabling derived_merge=on reduces execution time from ~21 seconds to ~5 seconds for the same query.

5. canned N databases

Shows how MySQL scans the information_schema tables; the number N (0, 1, ALL) indicates how many database directories are scanned.

localhost:information_schema>desc select * from tables\G
*************************** 1. row ***************************
  id: 1
  select_type: SIMPLE
  table: tables
  type: ALL
  Extra: Open_full_table; Scanned all databases
1 row in set, 1 warning (0.00 sec)

In MySQL 8.0 the data dictionary redesign changes the plan, using indexed views instead of full table scans.

The article concludes with links to previous issues and promotional information about the SQLE open‑source tool, documentation, and commercial support.