Bug #96931 Implement max_rows_examined limit as another optimizer hint

• Submitted: 18 Sep 2019 13:55
• Modified: 19 Sep 2019 6:51
• Reporter: Przemyslaw Malkowski
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[18 Sep 2019 13:55] Przemyslaw Malkowski

Description:
This is a feature request to introduce yet another limit option, in addition to existing MAX_EXECUTION_TIME (https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-execution-tim... and https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_...). 
It would be great to be able to cancel a statement execution if examined more then N rows. This would not only help to maintain stable server load but also to immediately find new bad queries.

MariaDB has already a similar option as addition to the LIMIT clause: https://mariadb.com/kb/en/library/limit-rows-examined/

How to repeat:
As of latest MySQL versions, we can only restrict query execution by putting time limit on it. While in some cases it could be very handy to restrict also by other measures, and rows examined would be probably the most useful one.

Suggested fix:
Implement max_rows_examined optimizer hint and system variable.

[19 Sep 2019 6:50] MySQL Verification Team

Hello Przemyslaw,

Thank you for the feature request.

regards,
Umesh