Bug #105282 Better control over records_in_range is needed to guide the optimizer
Submitted: 20 Oct 2021 17:45 Modified: 20 Oct 2021 20:25
Reporter: Yves Trudeau (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[20 Oct 2021 17:45] Yves Trudeau 
Description:
Often the optimizer makes very choices because the records in range estimate is poor. This can transform a short range scan into a lenghty table scan. This is especially true for composite primary keys and large tables. 

Another issue with records in range occurs when there are many partitions and many ranges. Evaluating records_in_range in quick_select for 1000 ranges on 40 partitions takes a long time, a time that can be longer than the actual query.

These two issues can be solved by the attached patch (8.0.26). The patch allows to specify the records_in_range value for a query as a comment hint like:

select /*+ RECORDS_IN_RANGE(10) */ ....

When the hint is present, a call to records_in_range returns the hint value instead of calling the handler::records_in_range function.

How to repeat:
It is a feature requests.
[20 Oct 2021 17:46] Yves Trudeau 
Records in range optimizer hint for 8.0.26

Attachment: RIR_8_0_26.diff (text/x-patch), 12.95 KiB.
[20 Oct 2021 20:25] MySQL Verification Team 
Thank you the feature request and contribution patch,