Wrong index used in SELECT , ROW_NUMBER() OVER (PARTITION BY
Hi! It appears MySQL 8.0.23 optimizer uses wrong index in queries using SELECT * , ROW_NUMBER() OVER (PARTITION BY ....
How do you index the table for my query? Here's the modified DDL and DML
-- DDL:
----------------------------
CREATE TABLE `test_table` (
`client_id` int DEFAULT NULL,
`division_id` int DEFAULT NULL,
`some_val` float DEFAULT NULL,
`end_date` timestamp NULL DEFAULT NULL,
`department_id` int DEFAULT NULL,
`some_data` varchar(45) DEFAULT NULL,
KEY `idx_client_enddate_division` (`client_id`,`end_date`,`division_id`),
KEY `idx_some_val` (`some_val` DESC),
KEY `idx_client_division` (`client_id`,`division_id`),
KEY `idx_client_department` (`client_id`,`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`client_id`)
(PARTITION part_null VALUES IN (NULL) ENGINE = InnoDB,
PARTITION part_neg_one VALUES IN (-1) ENGINE = InnoDB,
PARTITION a100 VALUES IN (100) ENGINE = InnoDB,
PARTITION a101 VALUES IN (101) ENGINE = InnoDB,
PARTITION a102 VALUES IN (102) ENGINE = InnoDB,
PARTITION a103 VALUES IN (103) ENGINE = InnoDB) */;
-- DATA
---------------------------------
INSERT INTO test_table(client_id, division_id, some_val, end_date, department_id, some_data) VALUES
(100, 1, 35.78, '2021-11-13 12:25:04', NULL, NULL),
(100, 1, 0.956245, '2022-04-09 05:24:48', NULL, NULL),
(100, 1, 0.861436, '2022-02-12 01:04:55', NULL, NULL),
(100, 1, 0.761067, '2023-07-29 05:49:36', NULL, NULL),
(100, 1, 0.707362, '2022-08-30 14:57:18', NULL, NULL),
(100, 1, 0.590262, '2022-03-24 01:41:34', NULL, NULL),
(100, 1, 0.848734, '2022-02-14 03:33:34', NULL, NULL),
(100, 1, 0.813579, '2022-01-30 14:22:59', NULL, NULL),
(100, 1, 0.819393, '2022-04-05 11:13:52', NULL, NULL),
(100, 1, 0.246256, '2021-12-29 21:55:56', NULL, NULL),
(100, 1, 0.304336, '2023-05-15 13:37:22', NULL, NULL),
(100, 1, 0.566081, '2021-11-11 23:25:52', NULL, NULL),
(100, 1, 0.0165217, '2022-11-19 20:34:28', NULL, NULL),
(100, 1, 0.21918, '2021-11-05 22:01:14', NULL, NULL),
(100, 1, 0.0157273, '2022-12-05 03:12:42', NULL, NULL),
(100, 1, 0.326197, '2022-10-16 16:49:26', NULL, NULL),
(100, 1, 0.056206, '2022-07-01 12:44:24', NULL, NULL),
(100, 2, 0.132138, '2022-02-20 04:37:58', NULL, NULL),
(100, 2, 0.00476808, '2021-12-11 07:20:57', NULL, NULL),
(100, 2, 0.901352, '2023-06-29 11:55:47', NULL, NULL),
(100, 2, 0.0827709, '2022-08-30 02:30:30', NULL, NULL),
(100, 2, 0.460482, '2022-12-06 22:16:54', NULL, NULL),
(100, 2, 0.00422428, '2023-07-23 02:45:55', NULL, NULL),
(100, 2, 0.935921, '2022-12-31 05:59:29', NULL, NULL),
(100, 2, 0.74449, '2022-10-05 02:56:12', NULL, NULL),
(100, 2, 0.722022, '2023-04-02 11:51:48', NULL, NULL),
(100, 3, 0.0180021, '2022-09-26 16:57:38', NULL, NULL),
(100, 3, 0.843845, '2022-07-16 09:28:08', NULL, NULL),
(100, 3, 0.871028, '2023-05-28 13:15:08', NULL, NULL),
(100, 3, 0.95494, '2021-08-24 08:33:43', NULL, NULL),
(100, 3, 0.304606, '2022-06-03 05:23:45', NULL, NULL),
(100, 3, 0.19306, '2022-12-24 11:27:13', NULL, NULL),
(100, 3, 0.926688, '2022-08-21 11:02:35', NULL, NULL),
(100, 3, 0.869524, '2023-02-03 20:43:32', NULL, NULL),
(100, 3, 0.180765, '2022-11-02 16:23:13', NULL, NULL),
(100, 3, 0.608817, '2021-11-19 14:48:45', NULL, NULL),
(100, 3, 0.940649, '2022-01-23 23:56:26', NULL, NULL),
(100, 3, 0.392966, '2022-01-18 19:32:53', NULL, NULL),
(100, 3, 0.00051388, '2022-03-02 21:56:07', NULL, NULL),
(100, 3, 0.473072, '2022-07-16 10:49:32', NULL, NULL),
(100, 3, 0.983735, '2022-07-13 18:15:20', NULL, NULL),
(100, 3, 0.433318, '2023-01-18 19:24:53', NULL, NULL),
(101, 7, 0.867828, '2023-02-22 21:51:02', NULL, NULL),
(101, 7, 0.31631, '2022-01-14 06:44:16', NULL, NULL),
(101, 7, 0.199429, '2022-03-12 13:21:03', NULL, NULL),
(101, 7, 0.942369, '2023-02-25 11:00:54', NULL, NULL),
(101, 7, 0.110144, '2021-12-15 03:06:48', NULL, NULL),
(101, 7, 0.611413, '2022-07-24 18:00:36', NULL, NULL),
(101, 7, 0.625555, '2022-11-18 12:09:26', NULL, NULL),
(101, 7, 0.382829, '2023-06-30 06:53:03', NULL, NULL),
(101, 7, 0.64375, '2022-04-07 07:22:21', NULL, NULL),
(101, 7, 0.785784, '2023-05-17 16:06:40', NULL, NULL),
(101, 7, 0.136147, '2023-07-19 09:44:43', NULL, NULL),
(101, 7, 0.514747, '2022-10-26 02:08:49', NULL, NULL),
(101, 7, 0.554862, '2023-05-29 11:04:18', NULL, NULL),
(101, 7, 0.909665, '2023-03-09 09:01:45', NULL, NULL),
(101, 11, 0.289879, '2021-08-27 09:53:48', NULL, NULL),
(101, 11, 0.320718, '2022-07-22 20:45:40', NULL, NULL),
(101, 11, 0.484727, '2023-06-28 03:22:58', NULL, NULL),
(101, 11, 0.323356, '2023-01-29 07:24:45', NULL, NULL),
(101, 11, 0.781222, '2022-11-21 00:22:52', NULL, NULL),
(101, 11, 0.933015, '2022-12-22 22:31:01', NULL, NULL),
(101, 11, 0.696283, '2022-05-07 02:22:56', NULL, NULL),
(101, 11, 0.83224, '2021-08-05 20:35:50', NULL, NULL),
(101, 11, 0.546006, '2022-12-26 19:53:45', NULL, NULL),
(101, 11, 0.883958, '2022-03-11 10:38:08', NULL, NULL),
(101, 11, 0.881157, '2022-07-20 08:59:56', NULL, NULL),
(101, 11, 0.786355, '2022-07-11 13:14:47', NULL, NULL),
(101, 11, 0.0103294, '2022-11-02 12:40:47', NULL, NULL),
(101, 11, 0.119066, '2022-12-27 11:41:08', NULL, NULL),
(101, 11, 0.169285, '2023-01-14 21:53:42', NULL, NULL),
(102, 3, 0.379421, '2022-12-15 22:34:06', NULL, NULL),
(102, 3, 0.309135, '2022-07-14 00:03:25', NULL, NULL),
(102, 3, 0.458774, '2023-04-20 23:57:12', NULL, NULL),
(102, 3, 0.934485, '2021-10-01 08:35:20', NULL, NULL),
(102, 3, 0.626253, '2023-04-29 05:35:52', NULL, NULL),
(102, 3, 0.488451, '2023-03-22 15:02:46', NULL, NULL),
(102, 3, 0.644282, '2023-02-02 05:29:07', NULL, NULL),
(102, 3, 0.845293, '2023-07-01 02:47:37', NULL, NULL),
(102, 3, 0.262038, '2022-06-10 13:41:56', NULL, NULL),
(102, 3, 0.371081, '2022-09-13 05:07:40', NULL, NULL),
(102, 3, 0.692191, '2023-02-18 08:07:28', NULL, NULL),
(102, 3, 0.811907, '2023-01-11 22:12:11', NULL, NULL),
(102, 3, 0.196504, '2023-03-08 22:43:33', NULL, NULL),
(102, 3, 0.426422, '2023-01-09 01:57:13', NULL, NULL),
(102, 3, 0.33348, '2022-07-30 10:34:25', NULL, NULL),
(102, 5, 0.498327, '2023-07-25 01:18:24', NULL, NULL),
(102, 5, 0.466895, '2022-04-17 11:39:14', NULL, NULL),
(102, 5, 0.386062, '2023-04-18 03:39:04', NULL, NULL),
(102, 5, 0.133126, '2021-10-04 22:28:22', NULL, NULL),
(102, 5, 0.0548398, '2021-07-31 20:06:42', NULL, NULL),
(102, 5, 0.84382, '2022-01-02 21:51:26', NULL, NULL),
(102, 5, 0.539021, '2021-09-07 04:16:06', NULL, NULL),
(102, 5, 0.647028, '2021-09-24 14:22:20', NULL, NULL),
(102, 5, 0.442331, '2023-07-17 04:38:53', NULL, NULL),
(102, 5, 0.581043, '2023-07-01 16:57:05', NULL, NULL),
(102, 5, 0.0588302, '2022-05-28 02:14:01', NULL, NULL),
(102, 5, 0.88839, '2021-12-25 19:51:54', NULL, NULL),
(102, 5, 0.349947, '2021-11-10 07:19:22', NULL, NULL),
(102, 5, 0.653479, '2023-04-08 18:20:14', NULL, NULL),
(102, 5, 0.266575, '2023-03-04 04:29:29', NULL, NULL),
(102, 5, 0.18361, '2022-08-20 02:17:14', NULL, NULL),
(102, 5, 0.0894101, '2023-04-21 13:01:31', NULL, NULL),
(102, 5, 0.0463059, '2022-11-11 20:53:06', NULL, NULL),
(102, 5, 0.0751218, '2022-06-22 01:34:27', NULL, NULL),
(102, 5, 0.0105604, '2022-12-31 19:55:54', NULL, NULL),
(102, 5, 0.524552, '2022-07-22 14:31:27', NULL, NULL),
(102, 5, 0.871445, '2023-05-11 10:33:45', NULL, NULL),
(102, 5, 0.836485, '2022-08-08 07:57:38', NULL, NULL),
(102, 5, 0.0502111, '2023-01-03 18:10:27', NULL, NULL),
(102, 5, 0.425646, '2023-07-17 19:39:04', NULL, NULL),
(102, 5, 0.635379, '2022-01-14 03:47:27', NULL, NULL),
(102, 5, 0.241381, '2022-08-13 01:09:25', NULL, NULL);
QUERY:
------------------------------------
explain SELECT m.*, ROW_NUMBER() OVER (PARTITION BY airline_id, end_date, fleet_id ORDER BY aqi DESC) AS rn
FROM pollutant_aggregated_data m
WHERE airline_id = 115
AND end_date <= '2022-07-30 14:53:27'
AND end_date >= '2020-07-30 14:53:27'
AND fleet_id is not null ;
So the optimizer picks idx_client_department instead of idx_client_enddate_division.
What am I doing wrong? How would you index this table for the aforementioned query?
Infinite thanks in advance.