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

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