MySQL using different index when direct index exist on columns

by Reddy   Last Updated June 30, 2020 04:06 AM

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.

Tags : mysql index


Related Questions



MySQL InnoDB Weird Query Performance

Updated June 10, 2020 06:06 AM



How mysql optimizer worked here?

Updated May 29, 2019 11:06 AM