I checked a myisam table index, the index is a copy of the whole field.
From: Reindl Harald [mailto:h.reindl@stripped]
Sent: Wednesday, February 12, 2014 10:02 AM
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?
Am 12.02.2014 02:48, schrieb Zhigang Zhang:
> I want to know the reason, in my opinion, to scan the smaller index data
> better performance than to scan the whole table data.
> From: Mathieu Desharnais [mailto:mdesharnais@stripped]
> Sent: Wednesday, February 12, 2014 9:41 AM
> To: Zhigang Zhang; mysql@stripped
> 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
> .. then your like would use the index.
> 2014-02-11 20:23 GMT-05:00 Zhigang Zhang <zzgang2008@stripped>:
> 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
> 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