MySQL Bugs: #66983: unnecessary "Using where " in explain result

Bug #66983	unnecessary "Using where " in explain result
Submitted:	27 Sep 2012 8:23	Modified:	7 Dec 2012 19:53
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ref, using where

Description:
There seems to be unnecessary "Using where" in the explain result, when using char column index or nullable column index.
This leads to extra re-revify after retriving data.

How to repeat:
CREATE TABLE `tmp_xf_like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT 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 * 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 |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * 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 | 9       | const |    1 | Using where |
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Can the two "using where" here can be removed?

In 5.6, Using where is gone in both queries.

For the first query, `title` = 'a' is pushed down to the index (ICP). This should be unnecessary.

For the second query, the predicate is no longer tested (correct).

Conclusion: there is a possible performance benefit in not pushing the condition down to the index in the first query.

Thank you for this performance bug.

The problem you mention is addressed in 5.6 : see :

http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

mysql> explain select * 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 index condition |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

Here is the output in MySQL 5.6 :

mysql> explain select * 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 | 9       | const |    1 | NULL  |
+----+-------------+-------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

However I am going to check if there is already a bug for the unnecessary ICP above. If not, I'll create one.

see Bug #67006      unnecessary use of ICP for a single predicate

Note that if we disable use of Index condition pushdown (in MySQL 5.6) the "Using where" will return for the first of the two queries:

set optimizer_switch="index_condition_pushdown=off";

explain select * 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

Noted in 5.7.0 changelog.

For queries using ref access on string data types, the ref access
condition could be evaluated again as part of the query condition or
pushed down as an index condition to the storage engine.

Bug#67006 was marked as a duplicate.