Does index range scan cause 'Using index condition'?

Question

When only cluster key and idx_price exist as indexes..

Query: select id , name from product where price < 20000

Explane:

detail	value
id	1
select_type	SIMPLE
table	product
partitions	NULL
type	range
possible_keys	idx_price
key	idx_price
key_len	9
ref	NULL
rows	7
filtered	100.00
Extra	Using index condition
1 row in set 1 warning (0.00 sec)

As far as I know, the Using index condition occurs depending on the presence or absence of the index of where clause.

But I just created and used idex_price for where clause.

What did I miss?

Answer 1

"Extra: Using index condition" means that it's making use of Index Condition Pushdown Optimization. It's using the idx_price index to optimize the price < 20000 condition in the WHERE clause. Instead of scanning the table data to find the rows that satisfy the condition, it only scans the index.

"Type: range" means that only rows in the range < 20000 are retrieved. Since indexes are B-trees, range conditions are easily optimized. In this case it can search the B-tree for 20000 and then return all the rows before this in the tree.

Answer 2

Before ICP (see Barmar's answer), the "Handler" took care of some of the filtering. With the Pushdown, the storage engine (InnoDB) does more of work. The performance benefit comes from not going back and forth between the Handler and the Storage Engine.

ICP, I think, only refers to the WHERE clause.

"Using index" is a better Extra -- It says that all the columns anywhere in the SELECT were found in the INDEX. That is, the query can be completely performed using only the index's BTree -- no bouncing back and forth between that and the data's BTree.