Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.27, 5.3.8, 5.2.12, 5.1.62
-
Fix Version/s: 5.5
-
Component/s: None
-
Labels:None
-
Environment: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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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); }