| 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: | |
| 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 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)
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