mysqldjangomysql-5.6mysql-5.5mysql-5.1

MySQL LIKE Query changes in 5.1 to 5.6 to full table scan

For some reason MySQL 5.6 does a full table scan, while MySQL 5.1 doesn't. Why is this happening?

EDIT: Both tables have the same exact indices

On MySQL 5.1

    mysql> explain SELECT `kv`.`key`, `kv`.`value` FROM `kv` WHERE `kv`.`key` LIKE BINARY 'weit-149411-%';
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | kv           | range | PRIMARY       | PRIMARY | 192     | NULL |  182 | Using where |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+

On MySQL 5.6

    mysql> explain SELECT `kv`.`key`, `kv`.`value` FROM `kv` WHERE `kv`.`key` LIKE BINARY 'weit-149411-%';
+----+-------------+--------------+-------+---------------+---------+---------+------+-------------+-------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows        | Extra       |
+----+-------------+--------------+-------+---------------+---------+---------+------+-------------+-------------+
|  1 | SIMPLE      | kv           | ALL   | PRIMARY       | NULL    | NULL    | NULL |  108364642  | Using where |
+----+-------------+--------------+-------+---------------+---------+---------+------+-------------+-------------+
Solution

  • Turns out that this is the intended effect. Starting in MySQL 5.5 LIKE BINARY no longer uses available indexes

    I am making a Django query that queries with key__startswith 1 which does a BINARY query by default.

    I was able to solve this issue by doing key__istartswith which does a case insensitive query on the key. This is fine for my use case since everything is lower case.