Bug #70586 Adjust eq_range_index_dive_limit default value
Submitted: 10 Oct 2013 11:30 Modified: 14 Jan 2014 18:10
Reporter: Jørgen Løland Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.6 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any

[10 Oct 2013 11:30] Jørgen Løland
MySQL 5.6 introduced the system variable eq_range_index_dive_limit which improves the time used to gather statistics if you have many values in an IN-list. However, if your data is skewed, it may also lead to less accurate #rows estimates and therefore a bad execution plan. 

Quite a few people in the MySQL community have complained that the default value is way too low. See e.g. www.facebook.com/note.php?note_id=10151533648715933. 

They have a good point, so we should do as advised. 

How to repeat:
See e.g. bug#70331

Suggested fix:
Increase the default value from 10 to 1000.
[14 Jan 2014 18:10] Paul Dubois
Noted in 5.7.4 changelog.

Based on community feedback, the default value of 10 for the
eq_range_index_dive_limit system variable has proven to be too low.
The default has been raised to 200.