List:General Discussion« Previous MessageNext Message »
From:Zhigang Zhang Date:February 12 2014 2:15am
Subject:RE: LIKE sql optimization
View as plain text  
I checked a myisam table index, the index is a copy of the whole field.

 

 

Zhigang

 

 

-----Original Message-----
From: Reindl Harald [mailto:h.reindl@stripped] 
Sent: Wednesday, February 12, 2014 10:02 AM
To: mysql@stripped
Subject: Re: LIKE sql optimization

 

because a index is not just a dumb copy of the whole field

and you simply can't seek in the middle of it?

 

http://en.wikipedia.org/wiki/B-tree

http://mattfleming.com/node/192

 

Am 12.02.2014 02:48, schrieb Zhigang Zhang:

> 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. 

> 

> 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.

 


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