List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:February 12 2014 1:37am
Subject:Re: LIKE sql optimization
View as plain text  

Am 12.02.2014 02:23, schrieb Zhigang Zhang:
> 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

because ‘%abcd’ can't work in case of a index
how do you imagine that?
read how a index works technically

'abcd%' may work but '%abcd' is impossible
independent what engine, this don't work and won't ever work

you may have luck with fulltext search (and it's other drawbacks)
https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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