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 = ?
CodePudding user response：
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
CodePudding user response：
The problem is not the data type or the collation, it's the fact that you're using OR to search two different columns.
Consider the analogy: suppose I ask you to find people named Gammel in the telephone book. You ask, "is that the last name or the first name?" I answer, "please find all cases, whether it's the first name or the last name."
Now you have a problem.
SELECT ... FROM telephone_book
WHERE last_name = 'Gammel' OR first_name = 'Gammel';
The book is sorted by last name, so it should be quick to find the entries that match the last name. But I also asked for all those that match the first name. Those may be distributed randomly all through the book, for people with any last name. You will now have to search the book the hard way, one page at a time.
A common solution to the OR optimization problem is to use UNION with two separate queries that search on respective indexes.
SELECT ... FROM telephone_book
WHERE last_name = 'Gammel'
UNION
SELECT ... FROM telephone_book
WHERE first_name = 'Gammel';
Supposing there is a different index on first_name, the latter query in this union will use it to find the entries matching by first name in an optimized way. We already know it can do that for last name.
Then once the (hopefully small) subset of rows matching either condition are found, these sets are unioned together into one result.