Why integer search string is slower on char field?

Question

I'm using MySQL version 5.1 at my workplace.

I have a column my_cloumn char(10) in my_table.

I recently found that a process was taking longer, and the SQL that was responsible for this was below:

SELECT * FROM my_table WHERE my_column = 12345, which can take some 3 minutes running.

Now, editing and using quotes on the search string this way

SELECT * FROM my_table WHERE my_column = '12345'

retrieves the record in a matter of micro seconds.

I do not have access (direct or indirect) to have the code changed to pass the queries with the quotes (by either direct sql or specifying the correct data type in PDO), and currently may not be able to change the column whilst users are using the database. What I need most is how to explain the difference above.

Why is this faster with quotes and not without?

Answer 1

The quotes define the expression as a string, whereas without the single quote it is evaluated as a number. This means that MySQL is forced to perform a Type Conversion to convert the number to a CHAR to do a proper comparison.

As the doc above says,

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup...

However, the inverse of that is not true and while the index can be used, using a string as a value causes a poor execution plan (as illustrated by jkavalik's sqlfiddle) where using where is used instead of the faster using index condition. The main difference between the two is that the former requires a row lookup and the latter can get the data directly from the index.

You should definitely modify the column data type (assuming it truly is only meant to contain numbers) to the appropriate data type ASAP, but make sure that no queries are actually using single quotes, otherwise you'll be back where you started.