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 16:41]
Domas Mituzas
[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)