if I have an int column of id, is it bad for performance to have a query written like
SELECT * FROM products where id = '1'?
Or does MySQL handle this the same as
SELECT * FROM products where id = 1
3 Answers
In general: don't do this. Compare values against a literal of the correct datatype, so you don't have to worry about what is going on under the hood. If you have an integer column, then:
SELECT * FROM products WHERE id = 1
In this specific situation: when asked to compare a number to a string, MySQL converts the string to a number. So there would be (almost) no performance penalty if you were to use:
SELECT * FROM products WHERE id = '1'
The literal string is converted once, and then checked against every value in the column.
On the other hand, consider a string column, say str, and a predicate like:
WHERE str = 1
Now it goes the other way around. MySQL needs to convert all string values in str to numbers before they can be compared. The expression becomes inefficient - it cannot take advantage of an index (it is non-SARGable). If there are many rows, the performance impact may be important.
So, back to the initial recommendation: know your datatypes; use the proper literal when comparing.
4 Comments
Ben
Why would MySQL not turn the 1 to '1' in your "WHERE str = 1 example?
GMB
@Ben: MySQL follows a set of predefined rules when asked to compare values of different datatypes. And the rule says that a string and an integer should be compared as numbers. See: dev.mysql.com/doc/refman/8.0/en/type-conversion.html. If you don't want to worry about this, use the proper datatype from the start, as recommended in the answer...
Ben
Thanks, just curious
Peter
Also mysql will produce
Truncated incorrect DOUBLE value warning if str is not a numberIf performance is your concern, numbers are smaller than text in this example--1 is faster than "1".
HOWEVER... if you're looking for performance never start with SELECT *
Comments
The cases:
WHERE int_col = 2 -- Good
WHERE int_col = "2" -- Good; the "2" is turned into INT at start of query
WHERE int_col = "2foo" -- Probably not what you wanted
WHERE char_col = 2 -- Slow; `char_col` is turned into INT for each row
WHERE char_col = "2" -- Good
int, so123 = '123foo'is true.idis aninttype. On general principles of Postel's Law, be strict in what you provide, so provide a comparison with the type that you are comparing to.