On 12/02/2014 13:16, Morgan Tocker wrote:
> Hi Zhigang,
>
> On Feb 11, 2014, at 8:48 PM, Zhigang Zhang <zzgang2...@gmail.com> 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.
In addition to what Morgan writes, then with an index scan you will end
up doing a lot of random I/O: even if the index scan itself is one
sequential scan (which is not guaranteed) then for each match, it will
be necessary to look up the actual row. On the other hand a table scan
will generally be more of a sequential read as you already have all the
data available for each match. Random I/O is more expensive than
sequential I/O - particularly on spinning disks - so in general the
optimizer will try to reduce the amount of random I/O.
In some cases though, you may see the index scan be preferred. Assume
you have a query like:
SELECT val FROM table WHERE condition LIKE '%abcd';
and you have an index (condition, val) or (val, condition) then the
whole query can be satisfied from the index (it's called a covering
index). In that case the index scan is usually preferred over the table
scan.
For the purpose of using an index to do index lookups to find the
matching rows rather than doing either a table or index scan for WHERE
clauses like "LIKE '%abcd'" you can do a couple of things:
* Duplicate the column used in the WHERE clause, but reverse the
string. That way the above WHERE clause becomes: WHERE
condition_revers LIKE 'dcba%'
This can use an index as it is a left prefix.
* If you always look for around the same number of characters at the
end in your WHERE clause, you can create a column with just those
last characters, e.g. so the WHERE clause becomes: WHERE
condition_suffix = 'abcd'
Do however be careful that you ensure you have enough selectivity
that way. If for example 90% of the rows ends in 'abcd' an index
will not do you much good (unless you are looking for the last 10%
of the rows).
Best regards,
Jesper Krogh
MySQL Support
