CODEWITHSUNDEEP
mysqlsqldatetimeindexing
miles
miles

Reputation: 53

MySQL not recognising datetime index in WHERE

What I'm trying to do is index the first name of a person and the date they were born.

The table is laid out like this:

CREATE TABLE test
(
 id INT NOT NULL AUTO_INCREMENT,
 fname VARCHAR(10) NOT NULL,
 sname VARCHAR(10) NOT NULL,
 age INT NOT NULL,
 born DATETIME NOT NULL,

 PRIMARY KEY(id),
 INDEX name_age(fname, sname, age),
 INDEX name_date(fname, born)
)

However the index isn't recognised in a where statement like so:

mysql> EXPLAIN SELECT * 
    -> FROM  `test` 
    -> WHERE fname =  "coby"
    -> AND born
    -> BETWEEN  "1900-05-02 06:23:00"
    -> AND  "2100-05-02 06:23:00";
+----+-------------+-------+------+--------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys      | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+--------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | name_age,name_date | name_age | 12      | const |   45 | Using where |
+----+-------------+-------+------+--------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

However it is recognised in an order by statement:

mysql> EXPLAIN SELECT * 
    -> FROM  `test` 
    -> WHERE fname =  "coby"
    -> ORDER BY born;
+----+-------------+-------+------+--------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys      | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+--------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | name_age,name_date | name_date | 12      | const |   45 | Using where |
+----+-------------+-------+------+--------------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

How do I make it so that the index is recognised in a where statement?

Any help would be appreciated.

Upvotes: 1

Views: 882

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65284

The index is recognized, as seen in the column possible_keys of the result of the first EXPLAIN statement. It just happens, that for 45 rows the other index produces an equal or better query plan: The selectivity of your date range is close to zero.

The ORDER BY is another pair of shoes: As you use the index not only for selection, but also for ordering, it now becomes useful.

Upvotes: 3

Related Questions