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

Question

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

Are the table definitions and contents otherwise identical? What storage engine? — eggyal, Jul 30 '14 at 13:37
yup everything is identical. Its a read replica that I promoted. same number of rows, same definitions and everything. (sorry didnt post those) — JiminyCricket, Jul 30 '14 at 13:38
Are your database character set and database collation the same as your table's? — Arth, Jul 30 '14 at 14:05

score 0 · Answer 1 · answered Aug 19 '14 at 15:01

0

The statistics you have on the 5.1 instance maybe stale and probably collected when the number of rows in that table was less.

Suggestion: on 5.6 , set global innodb_stats_persistent_sample_pages=300; analyze table ; and check the query execution.

answered Aug 19 '14 at 15:01

Prasad

1

score 0 · Accepted Answer · answered Jul 23 '16 at 14:35

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.

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

2 Answers2