MySQL Forums
Forum List  »  Partitioning

Wrong index used in SELECT , ROW_NUMBER() OVER (PARTITION BY
Posted by: Michael Zatkovetsky
Date: August 20, 2021 05:43PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Wrong index used in SELECT , ROW_NUMBER() OVER (PARTITION BY
1089
August 20, 2021 05:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.