Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.27, 5.3.8, 5.2.12, 5.1.62
-
None
-
None
-
all
Description
When using varchar/char type of index , there is excessive "Using where" in the result of explain command.
This cause unnecessary string comparison in query.
test case:
CREATE TABLE `tmp_xf_like` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`user_id` bigint(20) NOT NULL,
|
`title` varchar(128) NOT NULL,
|
`memo` varchar(2000) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `idx_userid` (`title`),
|
KEY `user_id` (`user_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
|
 |
mysql> explain select id from tmp_xf_like where title='a';
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 386 | const | 1 | Using where; Using index |
|
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
|
1 row in set (0.01 sec)
|
The result is the same after changing field "title" into binary
But INT type looks normal.
explain select id from tmp_xf_like where user_id=1;
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
| 1 | SIMPLE | tmp_xf_like | ref | user_id | user_id | 8 | const | 1 | Using index |
|
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
|
1 row in set (0.00 sec)
|