List:General Discussion« Previous MessageNext Message »
From:Zhigang Zhang Date:February 12 2014 2:23am
Subject:RE: LIKE sql optimization
View as plain text  
Thank you very much!

 

Zhigang

 

 

-----Original Message-----
From: Morgan Tocker [mailto:morgan.tocker@stripped] 
Sent: Wednesday, February 12, 2014 10:16 AM
To: Zhigang Zhang
Cc: mysql@stripped
Subject: Re: LIKE sql optimization

 

Hi Zhigang,

 

On Feb 11, 2014, at 8: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. 

 

I think I understand the question - you are asking why MySQL will not index
scan, find matching records, and then look them up rather than table scan?

 

I believe the answer is that there is no way of knowing if 1 row matches, or
all rows match.  In the worst case (where all rows match), it is much more
expensive to traverse between index and data rows for-each-record.

 

So a table scan is a “safe choice" / has less variance.=


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