Reputation: 1705
I Have gone through some Q&A eg.
How does database indexing work?
Mysql covering vs composite vs column index
These are good reads, But I got some more question about Indexes ie. Assuming below table and Execution plans:
CREATE TABLE student(`id` INT(9),
`name` VARCHAR(50),
`rollNum` INT(9),
`address` VARCHAR(50),
`deleted` int(2) default 0,
Key `name_address_key`(`name`,`deleted`),
Key `name_key`(`name`)
);
Plan 1: explain select * from student where name = "abc" and deleted =0;
its shows key = name_address_key
Plan 2: explain select * from student where name = "abc"
its also shows same key = name_address_key
my question is : How MySQl decide the index for execution plan?
Upvotes: 1
Views: 41
Reputation: 142560
I would expect it to pick name_key
since the size of the index is smaller.
I would recommend removing name_key
as being essentially useless, as @Barmar discusses.
Don't us 4-byte int
for flags (deleted
), see TINYINT
and other smaller datatypes.
Do have a PRIMARY KEY
.
Another good read (in my biased opinion): Indexing Cookbook
Upvotes: 1
Reputation: 782785
Since the name
column is a prefix of name_address_key
, that index can be used for matching name
just as well as name_key
can. There's no reason for it to prefer one over the other, and but the cardinality of name_address_key
is presumably higher, so it chooses that one.
There's no point in having name_key
, since it's redundant with name_address_key
and just wastes space.
Upvotes: 1