Bug #80829 Request for cost-based optimization of SELECT ... LIMIT N

• Submitted: 23 Mar 2016 0:13
• Modified: 29 Mar 2016 8:51
• Reporter: Mark Callaghan
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.6, 5.6.29
• OS: Any
• CPU Architecture: Any

[23 Mar 2016 0:13] Mark Callaghan

Description:
We debugged a bad query plan for the RocksDB storage engine for MySQL. The query is of the form SELECT ... FROM FOO FORCE INDEX (boo) WHERE x > 100 LIMIT 10. The cost for a full index scan was less than the cost for a range scan. Given the use of LIMIT N I hoped the range scan cost would then be adjusted in the code that considers LIMIT N optimizations, but according to SergeyP there isn't cost-based optimization for LIMIT N, and a full index scan was done.
https://github.com/facebook/mysql-5.6/issues/212#issuecomment-200062464

InnoDB has some clever code that always makes full range scan cheaper than full index scan. That looks like a kludge to me, but avoids the problem for InnoDB.

How to repeat:
Read https://github.com/facebook/mysql-5.6/issues/212

My reproduction case used MyRocks and iibench
https://github.com/mdcallag/mytools/tree/master/bench/ibench

Suggested fix:
Consider the impact of LIMIT N in the cost-based optimizer.

[29 Mar 2016 8:51] MySQL Verification Team

Hello Mark,

Thank you for the reasonable feature request!

Thanks,
Umesh