Question 245755 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Does index range scan cause 'Using index condition'?

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?

Tags:

2

Answers


  1. 0

    "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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search