suppose a table has only one index : idx_goods (is_deleted,price)
and is_deleted column is either 0 or 1

my query looks like this : where price < 10, which of the follwing behavior is this case:
1 mysql do a full scan on the secondary index for price match
2 mysql partially scan the secondary index for price match where it starts with is_deleted=0 in the secondary index, reach price=10, then jump to is_deleted=1 in the secondary index and continue there
3 mysql ignore the secondary index and scan the base table for price match, in other words, condition field that's not in the query's index key prefix is not matched against secondary index but against base table, event though the condition field is part of index key

CodePudding user response：

To utilize index based on multiple fields where condition must mostly use fields from the begining of index. So, if index is on fields (field1, field2, field3) then condition must contain field1.

More about MySQL index optimisation

There are some DB systems which can use indexes even if its first part is ommited in condition, but has very limited range of values in it.

Query plan can be checked by using EXPLAIN SELECT ..... documentation

For tables with small number of rows (fewer than 10.. from documentation ) indexes may be not used, so better prepare more data for real tests.

And as seen in this example index is not used for query

explain select * from test where price < 10

but for query below index is used with good results.

explain select * from test where is_deleted in (0,1) and price < 10

If is_deleted can be null then condition should be modified to (is_deleted in (0,1) or is_deleted is null), still it uses index.

And, as @Luuk mentioned, in this case (condition only on price field) index on price will be the best option.