| Bug #66825 | 5.5 ignores an index if BINARY is used | ||
|---|---|---|---|
| Submitted: | 14 Sep 2012 22:38 | Modified: | 15 Sep 2012 15:33 |
| Reporter: | Fernando Ipar (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Sep 2012 15:33]
MySQL Verification Team
Thank you for the bug report.
d:\dbs>5.1\bin\mysql -uroot sakila
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.66-Win X64-debug Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain select release_year from film where title like binary 'BR%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: idx_title
key: idx_title
key_len: 767
ref: NULL
rows: 9
Extra: Using where
1 row in set (0.01 sec)
mysql>
d:\dbs>5.5\bin\mysql -uroot sakila
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-debug Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain select release_year from film where title like binary 'BR%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: idx_title
key: NULL
key_len: NULL
ref: NULL
rows: 1002
Extra: Using where
1 row in set (0.00 sec)
d:\dbs>5.6\bin\mysql -uroot sakila
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.0-m10 Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain select release_year from film where title like binary 'BR%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: idx_title
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
Description: 5.5 seems to ignore an index if the where condition uses 'BINARY' (as for a case insensitive search), and it does a table scan instead. How to repeat: Using the sakila sample database, try this on 5.1 (I used 5.1.45): mysql [localhost] {msandbox} (sakila) > explain select release_year from film where title like binary 'BR%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: range possible_keys: idx_title key: idx_title key_len: 767 ref: NULL rows: 9 Extra: Using where 1 row in set (0.04 sec) And then on 5.5.27: mysql [localhost] {msandbox} (sakila) > explain select release_year from film where title like binary 'BR%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: idx_title key: NULL key_len: NULL ref: NULL rows: 1075 Extra: Using where 1 row in set (0.00 sec) It seems that doubling the condition, using binary only one time, is a workaround on 5.5.27: mysql [localhost] {msandbox} (sakila) > explain select release_year from film where title like 'BR%' and title like binary 'BR%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: range possible_keys: idx_title key: idx_title key_len: 767 ref: NULL rows: 9 Extra: Using where 1 row in set (0.00 sec)