MySQL Bugs: #68154: explain output 'Using index condition' not proper

Bug #68154	explain output 'Using index condition' not proper
Submitted:	23 Jan 2013 13:07	Modified:	24 Jan 2013 9:30
Reporter:	lou shuai (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.*	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	explain extra using index condition

Description:
When use explain for a range type select, the Extra column outputs 'Using index condition', which i think is not necessary and confuse me.

How to repeat:
use test;

CREATE TABLE t1(c1 int, c2 int, key(c1)) engine=INNODB;
INSERT INTO t1 values(1, 1);
INSERT INTO t1 values(2, 2);

explain select * from t1 where c1 > 10 and c1 < 11;
explain select * from t1 where c1 = 10;

IMO, the two explain above should perform the same value(NULL) in the EXTRA column. And it confuses me that a 'range' type select push the range condition down.

Suggested fix:
The Extra column for this type of select should be 'NULL'

Verified as described.

Impossible WHERE should be checked prior to setting "Extra" field to a value other then NULL.

@Sinisa Milivojevic

Maybe you misunderstood or my script not proper, i want to say 
explain select * from t1 where c1 > 1 and c1 < 100;
which output 'Using index condition' in EXTRA colum is not necessary.
 
And what you says may be another problem that for int column c1,
the condition 'c1 > n and c1 < n+1' should be Impossible.

During most of the optimization of these two queries the where
condition is kept as it is. As one of the very last steps the
optimizer does some optimizations for the query condition: For
multi-table queries it tries to move as much of the where condition to
as early as possible in the join order, for ref-access the ref
condition is removed from the query condition (in most cases), and the
optimizer tries to push as much as possible of the remaining query
condition down to the storage engine as an index condition.

For range access the optimizer currently does not have support for
eliminating the part of the query condition used as range
condition. Thus, for the query doing range scan, the query condition
will be evaluated for every record that is read. For a simple query
like the one above it would not be too hard to detect that this was
covered by the range condition, but for more complex conditions it
would be harder to determine which parts of the query condition that
could safely be eliminated.

You can see that the server will evaluate the query condition also
when not using index condition pushdown. If you disable use of ICP (by
set optimizer_switch='index_condition_pushdown=off';) or use MySQL 5.5
you will see "Using where" in the Extra column.

With ICP enabled, the query condition will by pushed down to the
storage engine. I agree that in this example this is unnecessary since
it will not filter out any records. Still, the overhead of doing this
is very small. The cost of evaluating the query condition in the
server or in the storage engine is the same. And it would be more
costly and complex to do the analysis of whether to push the condition
down, than to just unconditionally push the condition to the storage
engine. (it actually has a very minor performance effect, by pushing
the index condition to the storage engine, we will avoid
reading the first record that is after the range since the pushed index
condition will be evaluated using the index only).

The solution for avoiding the use of ICP in this case would be to
extend the optimizer to be able to eliminate the parts of the query
condition used by the range scan.