I checked a myisam table index, the index is a copy of the whole field.
Zhigang -----Original Message----- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 12, 2014 10:02 AM To: mysql@lists.mysql.com Subject: Re: LIKE sql optimization because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: > I want to know the reason, in my opinion, to scan the smaller index data has > better performance than to scan the whole table data. > > From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] > Sent: Wednesday, February 12, 2014 9:41 AM > To: Zhigang Zhang; mysql@lists.mysql.com > Subject: Re: LIKE sql optimization > > Sql database doesn't use index in like statement if it starts with % .. > > like 'abcd%' would work though... > > To use an index you can store your value using reverse function and index it > .. then your like would use the index. > > 2014-02-11 20:23 GMT-05:00 Zhigang Zhang <zzgang2...@gmail.com>: > > For example: > > Select * from T where col like ‘%abcd’; > > The table T is myisam table and we created a index on col. > > As we known, this like sql does not use the index created on col, it confuse > me, why? > > I think in mysiam engine, the index data is smaller, it can use index link > list to optimize it so as to reduce the disk scan than to the whole table > scan.
