1

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.

7
  • 1
    What is the cardinality of the status column? Aug 8, 2016 at 16:17
  • And what % of the rows in the table have status=1? Aug 8, 2016 at 16:17
  • 1
    @AndrewBrennan, Most likely everything will be 1, very very few 0, no 2
    – The Coder
    Aug 8, 2016 at 16:37
  • How many rows does the table have? Aug 8, 2016 at 19:56
  • @ypercubeᵀᴹ, currently < 100
    – The Coder
    Aug 9, 2016 at 4:23

1 Answer 1

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.