3

So I have a table with two indexes, one on a VARCHAR field (url) and one on an INT field (id). If I do a query on either of those fields using a WHERE condition with the usual =, the index is used in both cases. But for LIKE queries, the following uses the index:

SELECT * FROM user WHERE url LIKE 'somevalue';

But this one does not:

SELECT * FROM user WHERE id LIKE 1;

Is there an explanation somewhere in the docs for this or does anyone have any ideas why this is so?

  • 2
    Using LIKE on Integers? Hmm... – Philᵀᴹ Oct 20 '15 at 9:17
  • I know it doesn't make any sense, it's my ORM that generates this.. The main question is that I assumed MySQL would be smart enough to convert the query to a = lookup anyway – Martin Oct 20 '15 at 9:45
  • Get rid of that ORM! id LIKE 1 cannot us an index! – Rick James Oct 20 '15 at 17:45
7

To answer the question: http://sqlfiddle.com/#!9/1be04/2

The LIKE works on strings, so it has to CAST the id to a string - casting is a type of a function call (an expression) so index cannot be used.

I was not able to locate that specific information in the MySQL manual as it is an universal concept known as sargability (the article lists LIKE as sargable, but that holds only for string columns/indexes and prefix matching, not in general).

Another answer about what is and is not sargable: https://stackoverflow.com/a/799616/1786423

As ypercube pointed out in the comment - the 1 too is converted to '1' when the query is executed. It is done just once, not for every row, because the value is constant. It should not change the plan for this query but there are some cases when it can hurt you too - doing col IN (1,'2') will forbid the usage of any index no matter what type is set for the col.

  • 1
    "it has to CAST the id to a string". Correct. It also has to cast the 1 to a string. – ypercubeᵀᴹ Oct 20 '15 at 11:28
  • 1
    @ypercube yes, but that one does not affect the execution plan so I omitted it. Maybe I should not. – jkavalik Oct 20 '15 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.