[MDEV-553] Excessive "Using where" when using varchar/char type index Created: 2012-09-26  Updated: 2014-06-06

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.27, 5.3.8, 5.2.12, 5.1.62
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Xiaobin Lin (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 0
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)



 Comments   
Comment by Xiaobin Lin (Inactive) [ 2012-09-26 ]

In the function test_if_ref , the string type can not call store_val_in_field because of the logic :

if (field->binary() &&
field->real_type() != MYSQL_TYPE_STRING &&
field->real_type() != MYSQL_TYPE_VARCHAR &&
(field->type() != MYSQL_TYPE_FLOAT || field->decimals() == 0))

{ return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); }

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); }
Generated at Thu Feb 08 06:29:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.