Hello,
I'm using MySQL 5.5.8 and I've noticed a stragne bahaviour when it comes to the usage of my indices: I have a table that has no primary key but has a key on a datetime column. If I want to filter my table with a date, MySQL is not using that index. If I add a primary key or (very strage) if I change the connection-collation, the index on the datetime is used.
This can be reproduced with the following example:
mysql> CREATE TABLE `dates` (
`id` int(10) unsigned NOT NULL,
`date` datetime NOT NULL,
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
mysql> INSERT INTO dates (id,date) VALUES (1,'2012-09-11 00:00:00'),(2,'2012-09-11 01:00:00'),(3,'2012-09-11 02:00:00');
mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8_unicode_ci |
+------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM dates WHERE date='2012-09-11 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | dates | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
mysql> SET @@collation_connection='utf8_general_ci';
mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8_general_ci |
+------------------------+
mysql> EXPLAIN SELECT * FROM dates WHERE date='2012-09-11 00:00:00';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | dates | ref | date | date | 8 | const | 1 | |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
The same happens if I add a primary key for "id": With a primary key the "date"-Index is used, without it is not (sadly I can't do that because the table is maintained by some ORM-system). The problem is not limited to InnoDB, I have the same problem if I change the storage engine to MyISAM or use a different default collation.
Does anyone know something about this problem and can give me a hint?