Description:
[feature request] Some option to control whether partitions are scanned asc/desc would be useful in some circumstances to avoid a sort pass. Better yet, making the optimizer recognize that the sort can be handled by partitions could have mysql automatically read them in that order.
How to repeat:
Sample pulled from a system I was analyzing:
CREATE TABLE requests (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
day_num SMALLINT(5) UNSIGNED NOT NULL,
domain VARCHAR(253) DEFAULT NULL,
file_offset INT UNSIGNED NOT NULL,
primary key (id, day_num),
KEY requests__domain (domain)
) ENGINE = InnoDB DEFAULT CHARSET = latin1 ROW_FORMAT = COMPRESSED
PARTITION BY RANGE (dayId)
(PARTITION p_START VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p_15547 VALUES LESS THAN (15547) ENGINE = InnoDB,
PARTITION p_15554 VALUES LESS THAN (15554) ENGINE = InnoDB,
.....
PARTITION p_16058 VALUES LESS THAN (16058) ENGINE = InnoDB,
PARTITION p_16065 VALUES LESS THAN (16065) ENGINE = InnoDB,
PARTITION p_END VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
;
sample query run often:
SELECT r.day_num, r.file_offset
FROM requests r
WHERE domain LIKE %s
ORDER BY day_num DESC
LIMIT %d
if the order were asc, the implied ascending sort of the partitions would work, and the whole order clause could be removed. the partitioning could also be used to solve the desc use case, but mysql is unable to do so.
Suggested fix:
Make optimizer aware of partition order, ideally, or add syntax to force partition read order.
© 2025, Oracle Corporation and/or its affiliates
Description: [feature request] Some option to control whether partitions are scanned asc/desc would be useful in some circumstances to avoid a sort pass. Better yet, making the optimizer recognize that the sort can be handled by partitions could have mysql automatically read them in that order. How to repeat: Sample pulled from a system I was analyzing: CREATE TABLE requests ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, day_num SMALLINT(5) UNSIGNED NOT NULL, domain VARCHAR(253) DEFAULT NULL, file_offset INT UNSIGNED NOT NULL, primary key (id, day_num), KEY requests__domain (domain) ) ENGINE = InnoDB DEFAULT CHARSET = latin1 ROW_FORMAT = COMPRESSED PARTITION BY RANGE (dayId) (PARTITION p_START VALUES LESS THAN (0) ENGINE = InnoDB, PARTITION p_15547 VALUES LESS THAN (15547) ENGINE = InnoDB, PARTITION p_15554 VALUES LESS THAN (15554) ENGINE = InnoDB, ..... PARTITION p_16058 VALUES LESS THAN (16058) ENGINE = InnoDB, PARTITION p_16065 VALUES LESS THAN (16065) ENGINE = InnoDB, PARTITION p_END VALUES LESS THAN MAXVALUE ENGINE = InnoDB) ; sample query run often: SELECT r.day_num, r.file_offset FROM requests r WHERE domain LIKE %s ORDER BY day_num DESC LIMIT %d if the order were asc, the implied ascending sort of the partitions would work, and the whole order clause could be removed. the partitioning could also be used to solve the desc use case, but mysql is unable to do so. Suggested fix: Make optimizer aware of partition order, ideally, or add syntax to force partition read order.