Same reason as why composite index works only if you supply first field or fields ..
example index on a,b,c if you have a query : select * from tbl where a = 'whatever' and b = 'something it will use the index .. but a query like this one : select * from tbl where b = 'something' and c = 'something else' won't use the index .. ----- Just like an index in a book ... 2014-02-11 21:03 GMT-05:00 louis liu <yloui...@gmail.com>: > MySQL can't use index when '%xxxx' condition gives even oracle and you > can try full-text search > > > 2014-02-12 9:55 GMT+08:00 kitlenv <kitl...@gmail.com>: > >> *read how a index works technically* >> >> >> >> On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang <zzgang2...@gmail.com >> >wrote: >> >> > 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. >> > >> > >> > >> > >> > >> > zhigang >> > >> > >> > >> > _____ >> > >> > 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. >> > >> > >> > >> > Thanks. >> > >> > >> > >> > Zhigang >> > >> > >> > >> > >> > > > > -- > Phone: +86 18666668061 > Email & Gtalk: yloui...@gmail.com > Personal Blog: http://www.vmcd.org >
