eq_ range_index_dive _limit. The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used.
3/29/2014 · “eq_ range_index_dive _limit is helpful to reduce index dive costs on making query execution plans, but we think 10 is way too small.” Well… you got it! The new default in 5.7 is 200.
1/14/2014 · Description: 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.
8/23/2013 · The reason why estimated # of rows changes a lot is a new system variable eq_ range_index_dive _limit. As online manual says, if eq_ range_index_dive _limit is greater than 0, the optimizer uses existing index statistics instead of index dives if there are eq_ range_index_dive _limit or more equality ranges. Default eq_ range_index_dive _limit is 10.
If eq_ range_index_dive _limit is greater than 0, the optimizer uses existing index statistics instead of index dives if there are eq_ range_index_dive _limit or more equality ranges. Thus, to permit use of index dives for up to N equality ranges, set eq_ range_index_dive _limit to N + 1.
Apparently, 10.2 introduced a new variable eq_range_index_dives_limit, see MDEV-16934 but it hasn’t been documented yet. Please also note that in 10.4 its default is going to change: MDEV-17484.
As discussed in MDEV-17484, the value for eq_ range_index_dive _limit needs to be raised to 200.
Fixed, now server can be configured with eq_ range_index_dive _limit set in cnf file, eq_ range_index_dive _limit. Yes. event_scheduler. Yes. explicit_defaults_for_timestamp. Yes. flush. No. flush_time. Yes. ft_boolean_syntax. No. ft_max_word_len. Yes. ft_min_word_len. Yes. ft_query_expansion_limit. Yes. ft_stopword_file. Yes. general_log. Yes, -eq_ range_index_dive _limit=10 +eq_ range_index_dive _limit=200 Join buffer will auto-size itself-optimize_join_buffer_size=OFF +optimize_join_buffer_size=ON Use index statistics (cardinality) instead of records_in_range for large IN-lists – Just following MySQL here – …
