List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:February 12 2014 9:30am
Subject:Re: LIKE sql optimization
View as plain text  
On 12/02/2014 13:16, Morgan Tocker wrote:
> 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.

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


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