MySQL Forums
Forum List  »  Performance

Datetime-index not used with unicode connection-collation
Posted by: Michael Moench
Date: September 14, 2012 08:12AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Datetime-index not used with unicode connection-collation
3527
September 14, 2012 08:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.