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)
|
In the function test_if_ref , the string type can not call store_val_in_field because of the logic :
if (field->binary() &&
{ return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); }field->real_type() != MYSQL_TYPE_STRING &&
field->real_type() != MYSQL_TYPE_VARCHAR &&
(field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0))
So return 0, means the right_item is not removable.
This will lead to the afterward logic decides that checking the value is necessary, and then call add_found_match_trig_cond
tab->select->cond leads there is "Using where" shown in the result of explain and excessive comparison is later.
Perhaps we can simply remove the strint type in the above logic :
if (field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0)
{ return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); }