Why MySQL doesn't utilize leftmost Index field (UK - col1, col2, col3) in select query?

Question

I have a department table with the following structure

Create Table

CREATE TABLE `department` (
 `id` binary(16) NOT NULL,
 `name` varchar(255) NOT NULL,
 `type` int(11) NOT NULL,
 `status` tinyint(4) NOT NULL, -- Possible values are 0,1,2 only
 PRIMARY KEY (`id`),
 UNIQUE KEY `UK_2xsp2nild3xbgkg4pln7cviib` (`status`,`type`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Following query doesn't utilise the status index as it is the left most column in unique constraint.

EXPLAIN SELECT * FROM department d WHERE d.status = 1;
 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 
------ ----------- ------ ---------- ------ ---------------------------- ------ ------- ------ ------ -------- -------------
 1 SIMPLE d (NULL) ALL UK_2xsp2nild3xbgkg4pln7cviib (NULL) (NULL) (NULL) 4 100.00 Using where 

But when querying on another table which has unique constraint on a single column, it uses the index.

CREATE TABLE `account_unit` (
 `id` binary(16) NOT NULL,
 `status` tinyint(4) NOT NULL,
 `unit_name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UK_2x8b55sxftahu8cjbqld9sw9b` (`unit_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EXPLAIN SELECT * FROM account_unit au WHERE au.`unit_name` = 'AU1'
 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 
------ ----------- ------ ---------- ------ ---------------------------- ---------------------------- ------- ------ ------ -------- --------
 1 SIMPLE au (NULL) const UK_2x8b55sxftahu8cjbqld9sw9b UK_2x8b55sxftahu8cjbqld9sw9b 767 const 1 100.00 (NULL) 

As per my understanding, left most column on a multi column constraint will be utilized as index on select queries. Enlighten me if I'm wrong.

• Andrew Brennan: What is the cardinality of the status column?
• Andrew Brennan: And what % of the rows in the table have status=1?
• The Coder: @AndrewBrennan, Most likely everything will be 1, very very few 0, no 2
• ypercubeᵀᴹ: How many rows does the table have?
• The Coder: @ypercubeTM, currently < 100

Answer 1

It's almost certainly due to the low cardinality of the index on the first column. Since there are only 3 values, the optimizer decides that it is better do do a full scan on the table.

Using the index on the first column will point to about one third of the rows. Looking up that many rows based on the primary key will likely lead to practically all of the primary key pages being pulled into the buffer along with a large portion of the secondary index pages, leading to more memory usage and potentially disk traffic. Overall, it is more efficient and usually about the same speed to just do a full scan on the data if a large portion of the data is likely to be selected based on a secondary index lookup.