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

 

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?

    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.

    How "far" into the table is "快" vs "中"?

    If "中" was the first row looked at then only one row would need to be looked at for the 2nd and 3rd query.

    If the other was the last row then the entire table would need to be scanned.

    链接地址: http://www.djcxy.com/p/10960.html

    上一篇: 识别列表中的第一个和最后一个项目

    下一篇: 一个有趣的案例为mysql查询“喜欢'xx'”