MDEV-18551: New defaults for eq_range_index_dive_limitbb-mdev-18551

The value for eq_range_index_dive_limit is increased to 200.

7 files changed, 66 insertions, 10 deletions

diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 2c2f7be096d..154d0824182 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3027,5 +3027,23 @@ a	b
 set eq_range_index_dive_limit=default;
 drop table t1;
 #
+# MDEV-18551: New defaults for eq_range_index_dive_limit
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, key(a));
+insert into t1 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C;
+insert into t1 select 1 from ten A, ten B,ten C;
+create table t2(a int, key(a));
+insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + B.a*10 + C.a*100 < 199;
+analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	1502	1199.00	100.00	100.00	Using where; Using index
+insert into t2 values (200),(201);
+analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	201	1201.00	100.00	100.00	Using where; Using index
+drop table t1,ten,t2;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index bd2299bac5f..d4485292dc7 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2051,5 +2051,25 @@ set eq_range_index_dive_limit=default;
 drop table t1;
 
 --echo #
+--echo # MDEV-18551: New defaults for eq_range_index_dive_limit
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, key(a));
+insert into t1 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C;
+insert into t1 select 1 from ten A, ten B,ten C;
+
+create table t2(a int, key(a));
+insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + B.a*10 + C.a*100 < 199;
+
+let $a= `select group_concat(a) from t2`;
+eval analyze SELECT * FROM t1 where a in ($a);
+insert into t2 values (200),(201);
+let $a= `select group_concat(a) from t2`;
+eval analyze SELECT * FROM t1 where a in ($a);
+drop table t1,ten,t2;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 97f35bfe7b3..2aacf904941 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3039,6 +3039,24 @@ a	b
 set eq_range_index_dive_limit=default;
 drop table t1;
 #
+# MDEV-18551: New defaults for eq_range_index_dive_limit
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, key(a));
+insert into t1 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C;
+insert into t1 select 1 from ten A, ten B,ten C;
+create table t2(a int, key(a));
+insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + B.a*10 + C.a*100 < 199;
+analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	1502	1199.00	100.00	100.00	Using where; Using index
+insert into t2 values (200),(201);
+analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	201	1201.00	100.00	100.00	Using where; Using index
+drop table t1,ten,t2;
+#
 # End of 10.2 tests
 #
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result
index b8900afb774..1e969112db0 100644
--- a/mysql-test/main/range_vs_index_merge.result
+++ b/mysql-test/main/range_vs_index_merge.result
@@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
 (Name='Samara' AND Country='RUS') OR  
 (Name='Seattle' AND Country='USA');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	index_merge	Country,CountryPopulation,CountryName,CityName	CityName,CountryName	35,38	NULL	27	Using sort_union(CityName,CountryName); Using where
+1	SIMPLE	City	index_merge	Country,CountryPopulation,CountryName,CityName	CountryName,CityName	38,35	NULL	28	Using sort_union(CountryName,CityName); Using where
 SELECT Name, Country, Population FROM City WHERE
 (Name='Manila' AND Country='PHL') OR
 (Name='Addis Abeba' AND Country='ETH') OR          
@@ -1166,7 +1166,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE
 (Name='Samara' AND Country='RUS') OR  
 (Name='Seattle' AND Country='USA');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	range	Country,CountryPopulation,CountryName,CityName	CountryName	38	NULL	27	Using index condition; Using where
+1	SIMPLE	City	range	Country,CountryPopulation,CountryName,CityName	CountryName	38	NULL	29	Using index condition; Using where
 SELECT Name, Country, Population FROM City WHERE
 (Name='Manila' AND Country='PHL') OR
 (Name='Addis Abeba' AND Country='ETH') OR          
@@ -1922,6 +1922,6 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR
 Country='NOR' AND Name IN ('Oslo', 'Bergen') OR
 Country='ITA' AND Name IN ('Napoli', 'Venezia');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	City	range	CountryName,Name	CountryName	38	NULL	20	Using index condition; Using where
+1	SIMPLE	City	range	CountryName,Name	CountryName	38	NULL	22	Using index condition; Using where
 DROP DATABASE world;
 set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 926ac8b2668..451a1e259b5 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -881,10 +881,10 @@ ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	NULL
 VARIABLE_NAME	EQ_RANGE_INDEX_DIVE_LIMIT
-SESSION_VALUE	10
-GLOBAL_VALUE	10
+SESSION_VALUE	200
+GLOBAL_VALUE	200
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	10
+DEFAULT_VALUE	200
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	INT UNSIGNED
 VARIABLE_COMMENT	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.
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 219b550d83e..c809bf0fa6b 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -895,10 +895,10 @@ ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	NULL
 VARIABLE_NAME	EQ_RANGE_INDEX_DIVE_LIMIT
-SESSION_VALUE	10
-GLOBAL_VALUE	10
+SESSION_VALUE	200
+GLOBAL_VALUE	200
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	10
+DEFAULT_VALUE	200
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	INT UNSIGNED
 VARIABLE_COMMENT	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.
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 7241685fb61..86719ac4fd1 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2704,7 +2704,7 @@ static Sys_var_uint Sys_eq_range_index_dive_limit(
        "ranges for the index is larger than or equal to this number. "
        "If set to 0, index dives are always used.",
        SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG),
-       VALID_RANGE(0, UINT_MAX32), DEFAULT(10),
+       VALID_RANGE(0, UINT_MAX32), DEFAULT(200),
        BLOCK_SIZE(1));
 
 static Sys_var_ulong Sys_range_alloc_block_size(