Description:
MySQL 5.5 server can't see (and can't use) an index on a timestamp column if collation differs. Only MySQL 5.5 server is affected with this issue (MySQL 5.1, MySQL 5.6 works as expected).
See bellow tests:
1. Ubuntu 12.10/MySQL 5.5.29 - issue
2. Debian Squeeze/MySQL 5.5.28 compiled - issue
3. Debian Squeeze/MySQL 5.5.15 compiled - issue
4. Debian Lenny/MySQL 5.1.57 compiled - OK
5. Debian Squeeze/MySQL 5.5.30 MySQL binary - issue
6. Debian Squeeze/MySQL 5.6.10 MySQL binary - OK
How to repeat:
-- Creation Part --
DROP DATABASE IF EXISTS `dbtest`;
CREATE DATABASE `dbtest` COLLATE 'utf8_general_ci';
USE `dbtest`;
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` char(32) NOT NULL,
`updated` timestamp NOT NULL
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_general_ci';
ALTER TABLE `test` ADD INDEX `updated` (`updated`);
DELIMITER ;;
DROP PROCEDURE IF EXISTS fill;;
CREATE PROCEDURE fill()
BEGIN
DECLARE p1 INT DEfault 0;
simple_loop: LOOP
SET p1 = p1 + 1;
INSERT INTO `test` VALUES (NULL, MD5(RAND()), NOW() - INTERVAL (10000 + p1) SECOND);
IF p1 = 10000 THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END;;
DELIMITER ;
CALL fill();
-- Reproduce the issue on ANY MySQL 5.5 server:
-- Please update date/time constant to something like "NOW() - INTERVAL 500 SECOND" string constant before proceed.
mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > '2013-04-12 08:40:00';
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | range | updated | updated | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > NAME_CONST('x_updated',_utf8'2013-04-12 08:40:00' COLLATE 'utf8_general_ci');
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | range | updated | updated | 4 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > NAME_CONST('x_updated',_utf8'2013-04-12 08:40:00' COLLATE 'utf8_czech_ci');
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10306 | 100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
----
As you can see if collation differs from 'utf8_general_ci' no possible_key is available and FULL SCAN is made.
Suggested fix:
As done in MySQL 5.1 or MySQL 5.6 where this issue doesn't happen.
Description: MySQL 5.5 server can't see (and can't use) an index on a timestamp column if collation differs. Only MySQL 5.5 server is affected with this issue (MySQL 5.1, MySQL 5.6 works as expected). See bellow tests: 1. Ubuntu 12.10/MySQL 5.5.29 - issue 2. Debian Squeeze/MySQL 5.5.28 compiled - issue 3. Debian Squeeze/MySQL 5.5.15 compiled - issue 4. Debian Lenny/MySQL 5.1.57 compiled - OK 5. Debian Squeeze/MySQL 5.5.30 MySQL binary - issue 6. Debian Squeeze/MySQL 5.6.10 MySQL binary - OK How to repeat: -- Creation Part -- DROP DATABASE IF EXISTS `dbtest`; CREATE DATABASE `dbtest` COLLATE 'utf8_general_ci'; USE `dbtest`; CREATE TABLE `test` ( `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `msg` char(32) NOT NULL, `updated` timestamp NOT NULL ) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_general_ci'; ALTER TABLE `test` ADD INDEX `updated` (`updated`); DELIMITER ;; DROP PROCEDURE IF EXISTS fill;; CREATE PROCEDURE fill() BEGIN DECLARE p1 INT DEfault 0; simple_loop: LOOP SET p1 = p1 + 1; INSERT INTO `test` VALUES (NULL, MD5(RAND()), NOW() - INTERVAL (10000 + p1) SECOND); IF p1 = 10000 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END;; DELIMITER ; CALL fill(); -- Reproduce the issue on ANY MySQL 5.5 server: -- Please update date/time constant to something like "NOW() - INTERVAL 500 SECOND" string constant before proceed. mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > '2013-04-12 08:40:00'; +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | range | updated | updated | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > NAME_CONST('x_updated',_utf8'2013-04-12 08:40:00' COLLATE 'utf8_general_ci'); +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test | range | updated | updated | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN EXTENDED SELECT * FROM test WHERE updated > NAME_CONST('x_updated',_utf8'2013-04-12 08:40:00' COLLATE 'utf8_czech_ci'); +----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10306 | 100.00 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ---- As you can see if collation differs from 'utf8_general_ci' no possible_key is available and FULL SCAN is made. Suggested fix: As done in MySQL 5.1 or MySQL 5.6 where this issue doesn't happen.