List:General Discussion« Previous MessageNext Message »
From:louis liu Date:February 12 2014 2:03am
Subject:Re: LIKE sql optimization
View as plain text  
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 <kitlenv@stripped>:

> *read how a index works technically*
>
>
> On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang <zzgang2008@stripped
> >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: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
> > it
> > .. 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
> > 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:  ylouis83@stripped
Personal Blog: http://www.vmcd.org

Thread
LIKE sql optimizationZhigang Zhang12 Feb 2014
  • Re: LIKE sql optimizationReindl Harald12 Feb 2014
  • Re: LIKE sql optimizationMathieu Desharnais12 Feb 2014
    • RE: LIKE sql optimizationZhigang Zhang12 Feb 2014
      • Re: LIKE sql optimizationkitlenv12 Feb 2014
        • Re: LIKE sql optimizationlouis liu12 Feb 2014
          • Re: LIKE sql optimizationMathieu Desharnais12 Feb 2014
      • Re: LIKE sql optimizationReindl Harald12 Feb 2014
        • RE: LIKE sql optimizationZhigang Zhang12 Feb 2014
      • Re: LIKE sql optimizationMorgan Tocker12 Feb 2014
        • RE: LIKE sql optimizationZhigang Zhang12 Feb 2014
        • Re: LIKE sql optimizationJesper Wisborg Krogh12 Feb 2014
          • RE: LIKE sql optimizationZhigang Zhang12 Feb 2014