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