JiminyCricket JiminyCricket - 4 months ago 12
SQL Question

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 |
+----+-------------+--------------+-------+---------------+---------+---------+------+-------------+-------------+

Answer

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.