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

 


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