Bug #109612 Skipping records in range for queries with FORCE INDEX

• Submitted: 12 Jan 21:13
• Modified: 13 Jan 22:04
• Reporter: Manuel Ung
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 8.0.31
• OS: Any
• CPU Architecture: Any

[12 Jan 21:13] Manuel Ung

Description:
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.

regards,
Umesh

[13 Jan 22:04] Manuel Ung

Thanks Umesh, enhancement sounds fine to me.