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
