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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5.27 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[14 Sep 2012 22:38] Fernando Ipar
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)
[15 Sep 2012 15:33] Miguel Solorzano
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)