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