Categories
Database Development

MySQL using different index when direct index exist on columns

I have following table (stripped off other columns, indexes for simplicity)

CREATE TABLE `issues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_id` int(11) NOT NULL,
  `number` int(11) DEFAULT '0',
  `title` varbinary(1024) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_issues_on_book_id_and_number` (`book_id`,`number`),
  KEY `index_issues_on_book_id_and_updated_at` (`book_id`,`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

My query:

EXPLAIN SELECT `issues`.* FROM `issues` WHERE `issues`.`book_id` = 1 
AND `issues`.`updated_at` BETWEEN '2020-06-01 08:17:03' AND '2020-06-30 08:17:03'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: issues
   partitions: NULL
         type: ref
possible_keys: index_issues_on_book_id_and_number,index_issues_on_book_id_and_updated_at
          key: index_issues_on_book_id_and_number
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Why it’s not using index_issues_on_book_id_and_updated_at when the index is created on the exact columns that I want to query on.

Leave a Reply

Your email address will not be published. Required fields are marked *