Bug #76030 Add a way to disable rowid-ordered retrieval to optimizer_switch
Submitted: 24 Feb 2015 15:03
Reporter: Valeriy Kravchuk Email Updates:
Status: Open Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: ROR, rowid-ordered retrieval, use_index_extenstions

[24 Feb 2015 15:03] Valeriy Kravchuk
It seems that in some cases in MySQL rowid-ordered retrieval used internally may cause memory leaks. Check, for example, this old bug, http://bugs.mysql.com/bug.php?id=59316.

While this specific bug must be fixed in recent versions, and in suspicious cases workarounds like switching index_merge or use_index_extensions OFF may help, it would be still nice to get a separate switch to disable rowid-ordered retrieval (ROR) check entirely and just assume that for every index ROR is impossible. This may save some time at optimization checks and prevent memory leaks, while still allowing to use index merge and/or index extensions for other kinds of access.

How to repeat:
This is a feature request. Try to make sure rowid-ordered retrieval check is NOT performed for specific index (or all of them) without this feature. 

Imagine several huge IN lists/a lot of range intervals to check, and many indexes that optimizer has to check, with use_index_extensions=ON by default that in 5.6 makes them all candidates for ROR.

Suggested fix:
Implement separate switch for ROR for optimizer_switch, like "use_ror", ON by default, but with the ability to switch it to OFF.
[8 Jun 2015 22:10] Trey Raymond
This does crash production systems using such patterns, so it's more a bug than a feature request