Description:
plan is not optimal when comparing varchar column and int constant is used:
Non-optimal plan:
mysql> explain SELECT id FROM objects_resources WHERE url=123202;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | objects_resources | ALL | u | NULL | NULL | NULL | 10624 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Optimal plan:
mysql> explain SELECT id FROM objects_resources WHERE url='123202';
+----+-------------+-------------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | objects_resources | ref | u | u | 258 | const | 1 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)
How to repeat:
CREATE TABLE `objects_resources` (
`id` int(11) NOT NULL default '0',
`url` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `u` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
insert into objects_resources values (1, '1');
insert into objects_resources values (2, '2');
insert into objects_resources values (3, '3');
explain select id from objects_resources where url=1;
explain select id from objects_resources where url='1';
Suggested fix:
convert int constant to varchar first, and use it for index lookup
