MySQL key index not working, search all rows using where

Question

So basically I created a table:

CREATE TABLE IF NOT EXISTS `student` (
    `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
    `campus` enum('CAMPUS1', 'CAMPUS2') NOT NULL,
    `fullname` char(32) NOT NULL,
    `gender` enum('MALE', 'FEMALE') NOT NULL,
    `birthday` char(16) NOT NULL,
    `phone` char(32) NOT NULL,
    `emergency` char(32) NOT NULL,
    `address` char(128) NOT NULL,

    PRIMARY KEY (`idx`),
    KEY `key_student` (`campus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

I have like 20 rows with only 12 in CAMPUS1

But when I use query it: SELECT * FROM student WHERE campus='CAMPUS1'; The EXPLAIN is this:

id  select_type   table   type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE        student ALL   key_student    NULL NULL     NULL 20    Using where

I am new to this thing, how does a KEY really works? I read documentation but I cant understand that much.

On such a small table the optimizer probably thinks it is less expensive to scan the whole table rather than access the data via the index. — P.Salmon, Sep 04 '18 at 06:42

score 0 · Accepted Answer · answered Sep 04 '18 at 13:07

MySQL is trying to be smart (with varying success) when deciding which index to use for a query.

There are cases where it is faster to query the entire table instead of using the index. E.g: if your table has 500 records for CAMPUS1 and 100 records for CAMPUS2 it is faster to do a full (600 records) scan when looking for campus='CAMPUS1'.

When you have only 20 rows you run into the edge cases of the algorithm. Try adding some more rows, and see what happens.

Also, it seems this index will have a very low cardinality (an even split between only 2 values). It will probably not be very useful.

MySQL key index not working, search all rows using where

1 Answers1