CODEWITHSUNDEEP
mysqlsqlstringquery-optimizationwhere-clause
Ben
Ben

Reputation: 377

Is it a problem to compare a string to an INT column in MySQL

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

Upvotes: 1

Views: 1329

Answers (3)

Rick James
Rick James

Reputation: 142208

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

Upvotes: 0

Jim VanPetten
Jim VanPetten

Reputation: 436

If 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 *

Upvotes: 0

GMB
GMB

Reputation: 222402

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.

Upvotes: 2

Related Questions