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

[27 Sep 2012 8:23] xiaobin lin
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?
[27 Sep 2012 8:46] Jørgen Løland
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.
[27 Sep 2012 10:21] Arnaud Adant
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.
[28 Sep 2012 10:30] Arnaud Adant
see Bug #67006      unnecessary use of ICP for a single predicate
[3 Oct 2012 13:10] Olav Sandstå
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
[7 Dec 2012 19:53] Paul Dubois
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.
[10 Jan 2013 13:59] Erlend Dahl
Bug#67006 was marked as a duplicate.