MySQL Bugs: #64998: Index on TIMESTAMP not used depending on collation

Bug #64998	Index on TIMESTAMP not used depending on collation_connection
Submitted:	16 Apr 2012 20:47	Modified:	25 Jun 2013 16:59
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.20, 5.5.24	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
An index on a TIMESTAMP column is used just fine with collation_connection=utf8_general_ci but is not considered
with collation_connection=utf8_unicode_ci

This only seems to happen with 5.5, 5.1.60 and 5.6.4m7 don't seem to be affected.

How to repeat:
see attached test case

Suggested fix:
revert what broke this between 5.1 and 5.5, or backport whatever fixed it again in 5.6?

test case, run "make test"

Attachment: bug64998.tgz (application/x-compressed-tar, text), 1.18 KiB.

Verified just as described:

[openxs@chief mysql-test]$ ./mtr bug64998
Logging: ./mtr  bug64998
120417 10:50:20 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.24
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /home/openxs/dbs/5.5/mysql-test/var
Removing old var directory...
Creating var directory '/home/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 45722

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug64998                            [ fail ]
        Test ended at 2012-04-17 10:50:30

CURRENT_TEST: main.bug64998
--- /home/openxs/dbs/5.5/mysql-test/r/bug64998.result   2012-04-17 10:50:12.157822287 +0300
+++ /home/openxs/dbs/5.5/mysql-test/r/bug64998.reject   2012-04-17 10:50:30.149298895 +0300
@@ -32,4 +32,4 @@
 set collation_connection='utf8_unicode_ci';
 explain select * from t1 where datum > "2012-04-11 08:52:09";
 id     select_type     table   type    possible_keys   key     key_len ref    rows     Extra
-1      SIMPLE  t1      range   datum   datum   4       NULL    1       Using where
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    20      Using where

mysqltest: Result content mismatch
...

Just wanted to add to this bug. 

A client just recently upgraded from mysql 5.1.x to mysql 5.5.24 and we are seeing marked performance degradation. After detailed investigation we came to the same conclusion as this bug report: that differing connection collations are having a marked performance differences involving indexes on DATE and TIMESTAMP columns.

As an example:

Query:

SELECT pseudo_work.work_id AS sched_id, pseudo_work.time_in as sec_time_in, pseudo_work.time_out as sec_time_out, pseudo_work.lunch as sec_lunch, pseudo_work.break as sec_break, pseudo_work.time_worked as sec_time_worked, pseudo_work.work_date AS sched_date, pseudo_work.type, pseudo_work.phone_id AS phone_id_int, pseudo_work.location_ident, pseudo_work.program_ident, pseudo_work.manager_ident, pseudo_work.agent_id AS employee_ident, pseudo_work.gmt_time_in, pseudo_work.gmt_time_out, pseudo_work.switch AS switch_ident 
FROM pseudo_work 
LEFT JOIN programs ON programs.prog_id = pseudo_work.program_ident 
WHERE pseudo_work.location_ident = '110' AND (pseudo_work.program_ident IN ( '22104','9433','1755','476','22105','895','475','22101','22102','897','536','474','22103','473' )) AND (programs.client_ident IN ( '101','2748' )) 
AND pseudo_work.manager_ident = '153899' 
AND pseudo_work.work_date >= '2012-04-24' 
AND pseudo_work.work_date <= '2012-05-27' 
AND pseudo_work.gmt_time_in <= '1337961599' 
AND pseudo_work.gmt_time_out <= '1338220799' 
AND pseudo_work.gmt_time_out >= '1335369600'

Explain 1:

mysql> set collation_connection = 'utf8_unicode_ci';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: programs
         type: range
possible_keys: PRIMARY,programs_idx1
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 14
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pseudo_work
         type: ref
possible_keys: idx_pseudo_work_03,idx_pseudo_work_04,idx_pseudo_work_07,idx_pseudo_work_08,idx_pseudo_work_09
          key: idx_pseudo_work_08
      key_len: 5
          ref: ccms.programs.prog_id
         rows: 22950
        Extra: Using where
2 rows in set (0.00 sec)

Explain 2:

mysql> set collation_connection = 'utf8_general_ci';

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pseudo_work
         type: range
possible_keys: idx_pseudo_work_02,idx_pseudo_work_03,idx_pseudo_work_04,idx_pseudo_work_07,idx_pseudo_work_08,idx_pseudo_work_09
          key: idx_pseudo_work_07
      key_len: 8
          ref: NULL
         rows: 2909
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: programs
         type: eq_ref
possible_keys: PRIMARY,programs_idx1
          key: PRIMARY
      key_len: 4
          ref: ccms.pseudo_work.program_ident
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

On pseudo_work the optimizer switched from idx_pseudo_work_08 to idx_pseudo_work_07, resulting in a magnitude worse performance. 

We are currently investigating switching to utf8_general_ci as a temporary workaround. I believe as utf8_unicode_ci is used in a lot of examples, the severity of this bug should be increased.

Extended Hartmut's test case to include DATE and DATETIME columns. Run "make test".

Attachment: bug64998b.tgz (application/x-compressed-tar, text), 1.71 KiB.

--- bug64998.result	2012-05-30 13:16:11.035560244 -0400
+++ bug64998.reject	2012-05-30 13:21:06.869318316 -0400
@@ -34,7 +34,7 @@
 set collation_connection='utf8_unicode_ci';
 explain select * from t1 where datum > "2012-04-11 08:52:09";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	datum	datum	4	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
 CREATE TABLE t2 (
 id INT PRIMARY KEY AUTO_INCREMENT,
 datum DATE,
@@ -67,7 +67,7 @@
 set collation_connection='utf8_unicode_ci';
 explain select * from t2 where datum > "2012-04-11" AND datum < "2012-04-15";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	datum	datum	4	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
 CREATE TABLE t3 (
 id INT PRIMARY KEY AUTO_INCREMENT,
 datum DATETIME,
@@ -100,4 +100,4 @@
 set collation_connection='utf8_unicode_ci';
 explain select * from t3 where datum > "2012-04-11 08:52:09";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	range	datum	datum	9	NULL	1	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	20	Using where

mysqltest: Result content mismatch

http://bugs.mysql.com/bug.php?id=68942 marked as duplicate of this one.

This bug is fixed in 5.6.

It will NOT be fixed in 5.5, as a fix depends on the substantial amount of work invested in a WL entry that added some important infrastructure, that is missing in 5.5.