Why is using an INT to select a Varchar index containing numbers much slower than using Strings?

Question

I have a table that contains several thousand rows, having a Varchar column that contains numbers. Despite discussing why this column is not a numeric type then, selecting rows from that table showed a strange behavior.

Although there is an index on that column, using numeric strings to find a row is MUCH faster (0.01 secs) than using Ints (0.54 secs). What is the reason for this? It seems not to be able to cast and use the value for the index...

Am I overlooking something? It looks like it is not casting the Int to use it for the index? Do I have to give hints on index usage, or is there a database switch to accomplish this? Or if I misunderstand the Explain output, why is it so much slower then?

Table layout to show an example:

CREATE TABLE `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuff` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stuff` (`stuff`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here it is using the String for the index:

explain select * from example where stuff='200';
----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | example | ref  | idx_stuff     | idx_stuff | 137     | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+

Here it looks like it does not cast the Int to a String to use for looking up the index:

explain select * from example where stuff=200;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | example | index | idx_stuff     | idx_stuff | 137     | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+--------------------------+

Enwind

since your column is stored as a varchar searching as a string has to be faster. – Dizon

but it says "using index" and 200 could easiely be casted to string before. – Enwind

Answer 1

As stated in the manual:

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 in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

If necessary, you can always CAST your integer to a string in order to take advantage of the index:

SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);

Psychometry

Note: using CAST(200 AS CHAR) may not work to get MySQL to use the index. – Stockholder

@KevinBorders: Whyever would it not? – Psychometry

It turns out that this can happen if the character sets don't match. CONVERT(200 USING latin1) fixed it for me. – Stockholder

@KevinBorders: That's interesting. MySQL should transcode to the appropriate character set. What version? – Psychometry

This happened with 5.6.21 and a character_set_database of latin1 and the other character sets for client, connection, results, and system set to utf8. – Stockholder

hi eggyal, there's a saying that is the index added on the str_col not casted(str_col), so casted(ctr_col) don't have a index that cause the select not uses index. This saying seems make sense, but why int_col with a string value selection is using the index, don't it also have a "implicit convert" from int to string? – Hemialgia

@touchstone: Yes, a string search on an integer index does entail an implicit cast of the search parameter to an integer—but that cast is unambiguous, whereas casting the other way is not. – Psychometry

Answer 2

Warning: MySQL may also skip an index if its character set does not match, even if both values are CHAR. If the following query does not work:

SELECT * FROM example WHERE stuff = CAST(200 AS CHAR);

Then, get your database character set by running show variables like 'character_set_database'; and use that in a CONVERT statement as follows (this example assumes your database character set is latin1 -- replace that with your value of character_set_database):

SELECT * FROM example WHERE stuff = CONVERT(200 USING latin1);

Stockholder

Convert worked for me where CAST would not. Thank you! – Kloster