0

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?

Improve this question
5
  • If you don't have an index, it has to do a full scan to test the condition on every row. If you have an index, it uses the index. Isn't that what you expect? Commented Sep 8, 2023 at 17:17
  • This means it is using the index to satisfy your where clause, but still needs to access the row in the clustered index to read the other columns, as they are not contained in the index. If you add the name column to your index it would be "covering". See Using index and Using index condition. Commented Sep 8, 2023 at 17:34
  • @Barmar I wonder why 'Using index condition' occurs in that query Commented Sep 9, 2023 at 18:49
  • @user1191247 'Extra : Using index condition' is same 'Index Condition Pushdown(ICP)' ? If it means the same, why did 'ICP' occur in that query? Does index range scan cause 'ICP'? Commented Sep 9, 2023 at 19:00
  • @user1191247 As far as I know, 'ICP' can occur when columns that exist in 'where clause' do not exist in the index table. So why does ICP occur when only 'price' exists in the 'where clause' of that query? Commented Sep 9, 2023 at 19:06

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Understood. Thanks for the detail. I guess I lacked an understanding of the 'ICP',
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.

Improve this answer

3 Comments

Then, should I understand that the price<2000 clause caused 'ICP'?
If so, I was expecting using where, but I posted this question because "ICP" occurred.
@tray - Without name (and with the range test on price), the entire query could be performed in the index ("Using index"). Without the range test, there would be a table scan -- likely to be much slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.