CODEWITHSUNDEEP
mysqlindexing
wangs
wangs

Reputation: 1

I don't use the left most prefix of the indexes,why index is valid in result?

this is the sql

use test_01;
drop table if exists orders  ;
create table orders(
    id int auto_increment primary key ,
    name char(20) ,
    price decimal(10,2) default 0
);

alter table orders add key name_price_key(name,price);
explain select price from orders where price=45.80  and   name = 'alisi1900001';

this is the explain result

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL ref name_price_key name_price_key 87 const,const 1 100 Using where; Using index

Upvotes: 0

Views: 197

Answers (1)

Rick James
Rick James

Reputation: 142298

"Using index" is the clue. It means that all the columns in the SELECT are found in the index.

The leftmost column is in the WHERE:

and name = 'alisi1900001'

So, I don't understand what you are asking.

The order of the tests in WHERE does not matter; the order of the columns in INDEX does matter. The WHERE filters on both name and price, so the whole index can be used. Note also "const,const".

Upvotes: 0

Related Questions