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.