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

> 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