spraff spraff - 3 months ago 13
MySQL Question

Why are no keys used in this EXPLAIN?

I was expecting this query to use a key.

mysql> DESCRIBE TABLE Foo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+----------------+

mysql> EXPLAIN SELECT id FROM Foo WHERE name='foo';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+


Foo has a unique index on
name
, so why isn't the index being used in the
SELECT
?

Answer

From the MySQL Manual page entitled EXPLAIN Output Format:

Impossible WHERE noticed after reading const tables (JSON property: message)

MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

and the definition of const tables, from the Page entitled Constants and Constant Tables:

A MySQL constant is something more than a mere literal in the query. It can also be the contents of a constant table, which is defined as follows:

A table with zero rows, or with only one row

A table expression that is restricted with a WHERE condition, containing expressions of the form column = constant, for all the columns of the table's primary key, or for all the columns of any of the table's unique keys (provided that the unique columns are also defined as NOT NULL).

The second reference is a page and half long. Please refer to it.

const

const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;