Description:
MySQL can't use index on date column when collation_connection is set to any collation except for latin1 and binary.
How to repeat:
mysql> create table test_date_index (datecolumn date) ENGINE InnoDB;
Query OK, 0 rows affected (0.23 sec)
mysql> insert into test_date_index values ('2010-01-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_date_index values ('2010-01-02');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_date_index values ('2010-01-03');
Query OK, 1 row affected (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table test_date_index add unique index (datecolumn);
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test_date_index where datecolumn='2010-01-01';
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
| 1 | SIMPLE | test_date_index | index | NULL | datecolumn | 4 | NULL | 3 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from test_date_index where datecolumn = BINARY '2010-01-01';
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test_date_index where datecolumn='2010-01-01';
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> set names binary;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test_date_index where datecolumn='2010-01-01';
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.4-m3 |
+-----------+
1 row in set (0.00 sec)
Description: MySQL can't use index on date column when collation_connection is set to any collation except for latin1 and binary. How to repeat: mysql> create table test_date_index (datecolumn date) ENGINE InnoDB; Query OK, 0 rows affected (0.23 sec) mysql> insert into test_date_index values ('2010-01-01'); Query OK, 1 row affected (0.00 sec) mysql> insert into test_date_index values ('2010-01-02'); Query OK, 1 row affected (0.01 sec) mysql> insert into test_date_index values ('2010-01-03'); Query OK, 1 row affected (0.00 sec) mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) mysql> alter table test_date_index add unique index (datecolumn); Query OK, 0 rows affected (0.37 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test_date_index where datecolumn='2010-01-01'; +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ | 1 | SIMPLE | test_date_index | index | NULL | datecolumn | 4 | NULL | 3 | Using where; Using index | +----+-------------+-----------------+-------+---------------+------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from test_date_index where datecolumn = BINARY '2010-01-01'; +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> set names latin1; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from test_date_index where datecolumn='2010-01-01'; +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> set names binary; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from test_date_index where datecolumn='2010-01-01'; +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ | 1 | SIMPLE | test_date_index | const | datecolumn | datecolumn | 4 | const | 1 | Using index | +----+-------------+-----------------+-------+---------------+------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.4-m3 | +-----------+ 1 row in set (0.00 sec)