Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
1  
Never rely on implicit datatype conversion! Always use proper literals (strings enclosed with ', and numbers without) –  a_horse_with_no_name Jan 26 '12 at 11:46

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
That's it. I've just found the answer, but you were faster ;-) –  jcisio Jan 26 '12 at 12:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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