MySQL Bugs: #79073: Optimizer hint to disallow full scan

Bug #79073	Optimizer hint to disallow full scan
Submitted:	2 Nov 2015 13:09
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	full scan, hints, Optimizer

Description:
Make it possible to add an optimizer hint to a query to disallow a full scan.

This helps if range_optimizer_max_mem_size is set to low and the optimizer chooses a full scan. The optimizer should make the query fail in that case instead of doing a very expensive full table scan.

Related: Bug #70299

How to repeat:
Try to do a query which might need more memory for the range optimizer than configured and let it fail if it hits this limit.

Suggested fix:
"DELETE /*+ NO_FULL_SCAN */ FROM …"
"DELETE /*+ FORCE_RANGE */ FROM …"
"DELETE /* MAX_SCAN_ROWS(100000) */ FROM …"? 

Looks like this is the Oracle DBMS syntax:
"DELETE /*+ INDEX_RS_ASC(PRIMARY) */ FROM …"
This forces a ascending range scan

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0