Have you ever seen index which refused to be used even if there is every reason for it to work (from the glance view):
|
1 2 3 4 5 6 7 |
mysql> explain select * from article where article_id=10; +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | article | ALL | PRIMARY | NULL | NULL | NULL | 93490 | Using where | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec) |
Why on the earth index would not be used you would think, even if MySQL is mentioning it in “possible keys” ? Should you try to force it ?
|
1 2 3 4 5 6 7 |
mysql> explain select * from article force index (PRIMARY) where article_id=10; +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | article | ALL | PRIMARY | NULL | NULL | NULL | 93490 | Using where | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec) |
No Luck. Even Force Index can’t cure the problem. So what could it be ?
Lets take a look at article table:
|
1 2 3 4 5 |
CREATE TABLE `article` ( `article_id` varchar(20) NOT NULL, `dummy` varchar(255) NOT NULL default 'dummy', PRIMARY KEY (`article_id`) ) |
As you can see article_id is VARCHAR and this is the problem. Comparing String to Number is not going to use the index. Lets check if your guess is right:
|
1 2 3 4 5 6 7 |
mysql> explain select * from article where article_id="10"; +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | article | ref | PRIMARY | PRIMARY | 62 | const | 1 | Using where | +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) |
Looks much better does not it ?
So why you would define something as VARCHAR and when to refer to it as an INTEGER ? I see no good reason but It is quite frequently seen in applications. Might be designers just think – lets put it VARCHAR so it will fit strings if we need it to, but later decide to stick to numbers.
If you’re storing INTEGER it is much better to define your columns as INT for many reasons, but if you decided to go with VARCHAR (ie you need leading zeroes to be preserved) you should refer to it as a sting in your application by using “”.
You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ?
This can’t be done as it would result in wrong result for some queries. The thing is there are multiple strings possible for single integer value – for example for there is number 5 and strings “5”, “05” “0005” “5.0” which all have same numeric value but different strings – this is why simple coversion to the string does not work.
Interesing enough it works other way around – you can refer to integer column as a string in most cases and MySQL will use the index, as for any string there is only one number which matches it. I guess this causes a lot of confusions – having seen it working in one direction people assume it also works in reverse one.
To add confusion MySQL mentions key as “possible keys” while really it has no way to use it for lookup (it can do index scan though). I guess “possible keys” are calculated before type matching is checked.
So be careful to use matching reference types in your applications 🙂
