MySQL Indexes: composite vs column

Question

I Have gone through some Q&A eg.

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?

score 1 · Answer 1 · answered Feb 07 '17 at 12:30

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.

score 1 · Answer 2 · answered Feb 07 '17 at 19:39

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

MySQL Indexes: composite vs column

2 Answers2