Thank you very much!
From: Morgan Tocker [mailto:morgan.tocker@stripped]
Sent: Wednesday, February 12, 2014 10:16 AM
To: Zhigang Zhang
Subject: Re: LIKE sql optimization
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
> 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.=