Details
-
Task
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
Q4/2025 Server Development
Description
Consider a table partitioned by date:
CREATE TABLE t1 ( |
date_created DATETIME
|
user_id INT, |
...
|
INDEX idx1(user_id, date_created) |
) PARTITION BY RANGE COLUMNS(date_created) ( |
PARTITION `p_first` VALUES LESS THAN ('2022-02-24 00:00:00'), |
...
|
PARTITION `p_2025_02` VALUES LESS THAN ('2025-03-01 00:00:00'), |
...
|
PARTITION `p_last` VALUES LESS THAN ... |
);
|
And a query looking to get the last row describing a given user.
select * from t1 |
where user_id=1234 |
ORDER BY date_created DESC LIMIT 1 |
The index idx1(user_id, date_created) will allow to use ref access and SQL layer will make one index lookup.
However, ha_partition has only two algorithms:
- unordered index scan
- ordered index scan
Here, ordered index scan will be used. ha_partition will read a row from each partition and put that into the priority queue, then it will get the last one.
When there are many partitions, this will have a lot of overhead. Why can't we just read the row from partition p_last ? And after we've exhausted that, read the row from partition before the p_last and so forth?
(This is actually similar to what "unordered index scan" code does except that code starts from partitions with smaller number and continues to partitions with larger number)