Bug #109612 Skipping records in range for queries with FORCE INDEX
Submitted: 12 Jan 21:13 Modified: 13 Jan 22:04
Reporter: Manuel Ung Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 21:13] Manuel Ung
As part of WL#6526, we now skip records in range when FORCE INDEX is used.

However, this optimization is disabled if the query has an ORDER BY clause. It's not clear why this restriction exists, since the forced index either satisfies the  ORDER BY or not. This is a yes/no situation, so there's no costing involved, meaning there's no need to get stats from records in range.

How to repeat:
You can attach gdb, put a breakpoint at handler::records_in_range, and run any query with force index with/without ORDER BY.

However, this restriction is also documented in the worklog: https://dev.mysql.com/worklog/task/?id=6526

F1) For a single table query, index dives are skipped during execution if:
   a) FORCE INDEX applies to a single index.  
   b) No subquery is present.
   c) Fulltext Index is not involved.
   d) No GROUP-BY or DISTINCT clause.
   e) No ORDER-BY clause.

Suggested fix:
Remove this restriction in check_skip_records_in_range_qualification.

It's possible the same argument could apply to GROUP BY/DISTINCT, although haven't checked the code.
[13 Jan 8:54] MySQL Verification Team
Hello Manuel Ung,

Thank you for the report and feedback.
IMHO this should be treated as an enhancement since this restriction is documented in the worklog.

[13 Jan 22:04] Manuel Ung
Thanks Umesh, enhancement sounds fine to me.