When i query a varchar column (indexed) against an integer number, it runs extremely slow. I thought mysql was able to infer this and cast the parameter to string, but when i use an integer for filtering it avoids the index.
Is this ok? Is it a collation problem? Should i always cast manually integers to string for varchar index to work?
Running mysql 5.7
the varchar column is an external id, we do not control whether it’s integer or alphanumeric. Sometimes the user wants to find an object by our internal integer id, sometimes by their id, so we use: WHERE id = ? or external_id = ?
Advertisement
Answer
In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.
Since you’re comparing string column with an integer constant, MySQL will have to convert each value in the column to float for comparison and index might not be used (things might be different if it were the other way round i.e. integer column compared to string constant).
But more importantly, such comparison will produce unexpected results:
select '123abc' = 123 -- true
That being said, it is not very difficult to change this:
select '123abc' = 123 -- true
to this:
select '123abc' = '123' -- false