Entry

Why does EXPLAIN say "Impossible WHERE noticed after reading const tables"?

Does EXPLAIN work on a table with no rows?

Feb 8th, 2000 07:45
Nathan Wallace, Sasha Pachev, Benjamin Pflugmann

One of the first things the optimizer would do is to catch if there is
even a possiblity that something will match the WHERE clause.  For
example, if your table is empty then it is immediately obvious there
will be no matching rows...

EXPLAIN tells you what the optimizer would do. What the optimizer does
depends on the data - it behaves differently with different data sets,
therefore EXPLAIN only makes sense with data to work on.

Here is a simple example:

mysql> describe temp;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(12) | YES  |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.01 sec)

mysql> select * from temp;
Empty set (0.00 sec)

mysql> explain select id from temp where id = 3;
 ----------------------------------------------------- 
| Comment                                             |
 ----------------------------------------------------- 
| Impossible WHERE noticed after reading const tables |
 ----------------------------------------------------- 
1 row in set (0.01 sec)