MySQL Bugs: #109612: Skipping records in range for queries with FORCE INDEX

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

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.

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

Thanks Umesh, enhancement sounds fine to me.

Seems to have been fixed in https://github.com/mysql/mysql-server/commit/d911b9eb537b6a10231dd475c3f559ac13e631b9