Bug #54921 ORDER BY doesn't use index if ref lookup on VARBINARY column used
Submitted: 30 Jun 2010 16:41 Modified: 30 Jun 2010 17:10
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0-bzr, 5.1-bzr OS:Any
Assigned to: CPU Architecture:Any

[30 Jun 2010 16:41] Domas Mituzas
Description:
If 

* client collation is not binary
* indexed fields are binary
* there's a composite index
* ref lookup is done on leftmost part
* ORDER BY relies on full index

optimizer won't use the index, if client collation is not 'binary', though it will use index for:

*index scans
*full table scans
*explicit BINARY ref lookup
*client collation is binary

How to repeat:
mysql> create table tb (a varbinary(255), b varbinary(255), primary key (a,b));
mysql> insert into tb values ("a","b"),("a","c"),("e","f");

mysql> explain select * from tb where a="a" order by a, b;
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | tb    | ref  | PRIMARY       | PRIMARY | 257     | const |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+

mysql> explain select * from tb where a>="a" order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tb    | index | PRIMARY       | PRIMARY | 514     | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tb order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tb    | index | NULL          | PRIMARY | 514     | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tb where binary a="a" order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tb    | index | NULL          | PRIMARY | 514     | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tb where a="a" order by a, b;
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | tb    | ref  | PRIMARY       | PRIMARY | 257     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Suggested fix:
use index for sorting binary data when ref lookups are done by clients with non-binary character sets
[30 Jun 2010 17:10] Valeriy Kravchuk
Verified just as described:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create table tb (a varbinary(255), b varbinary(255), primary key (a,b));Query OK, 0 rows affected (0.07 sec)

mysql>  insert into tb values ("a","b"),("a","c"),("e","f");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from tb where a="a" order by a, b;
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | tb    | ref  | PRIMARY       | PRIMARY | 257     | const |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+---------+---------+-------+------+------------------------------------------+
1 row in set (0.03 sec)

mysql> explain select * from tb where a>="a" order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tb    | index | PRIMARY       | PRIMARY | 514     | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql>  explain select * from tb order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tb    | index | NULL          | PRIMARY | 514     | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql>  explain select * from tb where binary a="a" order by a, b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tb    | index | NULL          | PRIMARY | 514     | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.37 sec)

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------+
| Variable_name            | Value                                       |
+--------------------------+---------------------------------------------+
| character_set_client     | latin1                                      |
| character_set_connection | latin1                                      |
| character_set_database   | latin1                                      |
| character_set_filesystem | binary                                      |
| character_set_results    | latin1                                      |
| character_set_server     | latin1                                      |
| character_set_system     | utf8                                        |
| character_sets_dir       | /Users/openxs/dbs/5.1/share/mysql/charsets/ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)

mysql> set names binary;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from tb where a="a" order by a, b;+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | tb    | ref  | PRIMARY       | PRIMARY | 257     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
[13 Jul 2010 0:59] Omer Barnir
triage: setting to SRMRTBD  (borderline of I4 and checked)