a funny case for mysql query " like 'xx' "

Question

I found a funny case in MySQL query SQL with prefix string match;

I created table like this;

CREATE TABLE `EpgInfo` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `title` varchar(100)  NULL NOT NULL DEFAUL '',
   PRIMARY KEY (`id`),
   KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then, I inserted 1,000,000 rows of data for random column title which length is less than 20.

I use 3 SQLs like this:

• SQL #1: select * from EpgInfo2 where title like "快" limit 1;
• SQL #2: select * from EpgInfo2 where title = "中" limit 1;
• SQL #3: select * from EpgInfo2 where title like "中" limit 1;

And I found that:

• SQL #1 costs 0.2s.
• SQL #2 and SQL #3 cost 0.0s. I used show status like %handl% to found the query plan, I found SQL #1 scan all index data, SQL #2 and SQL #3 did not.

Why?

Answer 1

Use EXPLAIN to see how MySQL handles the queries, it might give you a clue.

Also, try some other characters. Maybe MySQL is misinterpreting one of those as having a percent sign in it.