Thand you very much!
From: Jesper Wisborg Krogh [mailto:mysql@stripped]
Sent: Wednesday, February 12, 2014 5:30 PM
To: Morgan Tocker; Zhigang Zhang
Subject: Re: LIKE sql optimization
On 12/02/2014 13:16, Morgan Tocker wrote:
On Feb 11, 2014, at 8:48 PM, Zhigang Zhang <mailto:zzgang2008@stripped>
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
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 =
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).