2

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?

4

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.

  • This is a great answer! "but make sure that no queries are actually using single quotes, otherwise you'll be back where you started" this is why I did not want to change it quickly, your explanation is good enough to have me explain why this needs the time it needs. Thanks – Gift Rise Dec 9 '15 at 15:26
  • 2
    @gift rise If you are accepting user data and putting it directly into a select then you have way bigger problems than speed. Look up sql injection. – paparazzo Dec 9 '15 at 18:27
  • @GiftRise AFAIK when using quotes when searching on an integer column MySQL CAN use index without problems - the problem with strings is that for example '123' and '0123' both are equal to 123 so you cannot tell which exact string values to get from the index. The other way around any such string will convert to exactly one integer 123 which is easily found in the index. – jkavalik Dec 9 '15 at 19:21
  • 1
    @JohnM not sure if I can find the exact part of documentation, but I prepared an example - sqlfiddle.com/#!9/eb300/1 - compare the explains and you can see that for the int column the quotes do not make difference whereas for the char column they do. – jkavalik Dec 9 '15 at 20:05
  • 1
    @JohnM I found an "authority" mentioning this distinction at least - percona.com/blog/2006/09/08/why-index-could-refuse-to-work - the paragraph beginning with Interesing enough it works other way around. – jkavalik Dec 9 '15 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.