MySQL text field without quote

I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table:

mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = 493457;
+----+-------------+-------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | scald_atoms | index | base_id       | base_id | 767     | NULL | 84404 | Using where; Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

With a quote, it is ok.

mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = '493457';
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | scald_atoms | const | base_id       | base_id | 767     | const |    1 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

That makes total sense.

Since you said the base_id is VARCHAR(255), your first query

SELECT sid FROM rf_fo.scald_atoms WHERE base_id = 493457;

really behaves like this

SELECT sid FROM rf_fo.scald_atoms WHERE CAST(base_id as unsigned) = 493457;

This causes every base_id to experience a CAST and a comparison. Full table scan required.

Your second query is a pure string comparison and the index has string-based values.

StackExchange Database Administrators Q#11623, answer score: 5