MySQL does not use index for wildcard-free query on an INT field - why?

Question

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?

Answer 1

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.