summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/mysqld--help.result2
-rw-r--r--mysql-test/main/range.result20
-rw-r--r--mysql-test/main/range.test23
-rw-r--r--mysql-test/main/range_mrr_icp.result20
-rw-r--r--mysql-test/main/range_vs_index_merge.result98
-rw-r--r--mysql-test/main/range_vs_index_merge_innodb.result60
6 files changed, 143 insertions, 80 deletions
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index a547a978a01..e130f324da0 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -1436,7 +1436,7 @@ encrypt-binlog FALSE
encrypt-tmp-disk-tables FALSE
encrypt-tmp-files FALSE
enforce-storage-engine (No default value)
-eq-range-index-dive-limit 10
+eq-range-index-dive-limit 200
event-scheduler OFF
expensive-subquery-limit 100
expire-logs-days 0
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index bcb43d1e905..9a2d99e2f82 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3100,6 +3100,26 @@ 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;
+# expected type=range, rows=1487 , reason=using index dives
+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 1487 1199.00 100.00 100.00 Using where; Using index
+insert into t2 values (200),(201);
+# expected type=range, rows=201 , reason=using index statistics
+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 global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 9edb3f39ae3..76d893f56b7 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2075,6 +2075,29 @@ 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`;
+
+--echo # expected type=range, rows=1487 , reason=using index dives
+eval analyze SELECT * FROM t1 where a in ($a);
+insert into t2 values (200),(201);
+let $a= `select group_concat(a) from t2`;
+--echo # expected type=range, rows=201 , reason=using index statistics
+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 5d55a820673..5cda4111b6d 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3097,6 +3097,26 @@ 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;
+# expected type=range, rows=1487 , reason=using index dives
+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 1487 1199.00 100.00 100.00 Using where; Using index
+insert into t2 values (200),(201);
+# expected type=range, rows=201 , reason=using index statistics
+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 global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result
index 82d2358cebc..efbf41d6372 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 range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 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
@@ -1109,33 +1109,33 @@ SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
Name Country Population
-Addis Abeba ETH 2495000
-Ankara TUR 3038159
-Bangalore IND 2660088
+Toronto CAN 688275
+Vancouver CAN 514008
Basel CHE 166700
-Caracas VEN 1975294
-Dakar SEN 785071
-Delhi IND 7206704
+Peking CHN 7472000
+Praha CZE 1181126
Dresden DEU 476668
+Addis Abeba ETH 2495000
+Paris FRA 2125246
Jakarta IDN 9604900
+Bangalore IND 2660088
+Delhi IND 7206704
+Teheran IRN 6758845
+Roma ITA 2643581
+Venezia ITA 277305
+Tokyo JPN 7980230
+Seoul KOR 9981619
Kaunas LTU 412639
+Rabat MAR 623457
+Tijuana MEX 1212232
Lagos NGA 1518000
-Lugansk UKR 469000
Manila PHL 1581082
-Paris FRA 2125246
-Peking CHN 7472000
-Praha CZE 1181126
-Rabat MAR 623457
-Roma ITA 2643581
Samara RUS 1156100
+Dakar SEN 785071
+Ankara TUR 3038159
+Lugansk UKR 469000
Seattle USA 563374
-Seoul KOR 9981619
-Teheran IRN 6758845
-Tijuana MEX 1212232
-Tokyo JPN 7980230
-Toronto CAN 688275
-Vancouver CAN 514008
-Venezia ITA 277305
+Caracas VEN 1975294
set optimizer_switch='index_merge=off';
EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') 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 CityName 35 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 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
@@ -1196,33 +1196,33 @@ SELECT Name, Country, Population FROM City WHERE
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
Name Country Population
-Addis Abeba ETH 2495000
-Ankara TUR 3038159
-Bangalore IND 2660088
+Toronto CAN 688275
+Vancouver CAN 514008
Basel CHE 166700
-Caracas VEN 1975294
-Dakar SEN 785071
-Delhi IND 7206704
+Peking CHN 7472000
+Praha CZE 1181126
Dresden DEU 476668
+Addis Abeba ETH 2495000
+Paris FRA 2125246
Jakarta IDN 9604900
+Bangalore IND 2660088
+Delhi IND 7206704
+Teheran IRN 6758845
+Roma ITA 2643581
+Venezia ITA 277305
+Tokyo JPN 7980230
+Seoul KOR 9981619
Kaunas LTU 412639
+Rabat MAR 623457
+Tijuana MEX 1212232
Lagos NGA 1518000
-Lugansk UKR 469000
Manila PHL 1581082
-Paris FRA 2125246
-Peking CHN 7472000
-Praha CZE 1181126
-Rabat MAR 623457
-Roma ITA 2643581
Samara RUS 1156100
+Dakar SEN 785071
+Ankara TUR 3038159
+Lugansk UKR 469000
Seattle USA 563374
-Seoul KOR 9981619
-Teheran IRN 6758845
-Tijuana MEX 1212232
-Tokyo JPN 7980230
-Toronto CAN 688275
-Vancouver CAN 514008
-Venezia ITA 277305
+Caracas VEN 1975294
set optimizer_switch=@save_optimizer_switch;
#
# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n
@@ -1900,23 +1900,23 @@ Country='NOR' AND Name IN ('Oslo', 'Bergen') OR
Country='ITA' AND Name IN ('Napoli', 'Venezia');
ID Name Country Population
175 Antwerpen BEL 446525
-2808 Bergen NOR 230948
+176 Gent BEL 224180
3068 Berlin DEU 3386667
3087 Bonn DEU 301048
-2918 Braga PRT 90535
-176 Gent BEL 224180
3242 Lahti FIN 96921
-3580 Moscow RUS 8389200
+2974 Paris FRA 2125246
1466 Napoli ITA 1002619
+1474 Venezia ITA 277305
+2808 Bergen NOR 230948
2807 Oslo NOR 508726
-2974 Paris FRA 2125246
+2928 Warszawa POL 1615369
+2931 Wroclaw POL 636765
+2918 Braga PRT 90535
2915 Porto PRT 273060
+3580 Moscow RUS 8389200
3581 St Petersburg RUS 4694000
3048 Stockholm SWE 750348
3051 Uppsala SWE 189569
-1474 Venezia ITA 277305
-2928 Warszawa POL 1615369
-2931 Wroclaw POL 636765
explain select * from City
where
Country='FIN' AND Name IN ('Lahti','Imatra') OR
@@ -1930,6 +1930,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 Name 35 NULL 20 Using index condition; Using where
+1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where
DROP DATABASE world;
set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result
index 6b6b77232e2..03b43431fe7 100644
--- a/mysql-test/main/range_vs_index_merge_innodb.result
+++ b/mysql-test/main/range_vs_index_merge_innodb.result
@@ -1085,7 +1085,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 CityName 35 NULL 27 Using index condition; Using where
+1 SIMPLE City index_merge Country,CountryPopulation,CountryName,CityName CountryName,CityName 38,35 NULL 27 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
@@ -1116,32 +1116,32 @@ SELECT Name, Country, Population FROM City WHERE
(Name='Seattle' AND Country='USA');
Name Country Population
Addis Abeba ETH 2495000
-Ankara TUR 3038159
-Bangalore IND 2660088
-Basel CHE 166700
-Caracas VEN 1975294
-Dakar SEN 785071
-Delhi IND 7206704
-Dresden DEU 476668
+Manila PHL 1581082
Jakarta IDN 9604900
+Delhi IND 7206704
+Bangalore IND 2660088
+Teheran IRN 6758845
+Roma ITA 2643581
+Venezia ITA 277305
+Tokyo JPN 7980230
+Toronto CAN 688275
+Vancouver CAN 514008
+Peking CHN 7472000
+Seoul KOR 9981619
Kaunas LTU 412639
+Rabat MAR 623457
+Tijuana MEX 1212232
Lagos NGA 1518000
-Lugansk UKR 469000
-Manila PHL 1581082
Paris FRA 2125246
-Peking CHN 7472000
+Dresden DEU 476668
+Dakar SEN 785071
+Basel CHE 166700
Praha CZE 1181126
-Rabat MAR 623457
-Roma ITA 2643581
+Ankara TUR 3038159
+Lugansk UKR 469000
+Caracas VEN 1975294
Samara RUS 1156100
Seattle USA 563374
-Seoul KOR 9981619
-Teheran IRN 6758845
-Tijuana MEX 1212232
-Tokyo JPN 7980230
-Toronto CAN 688275
-Vancouver CAN 514008
-Venezia ITA 277305
set optimizer_switch='index_merge=off';
EXPLAIN SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
@@ -1172,7 +1172,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 CityName 35 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 28 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
@@ -1906,23 +1906,23 @@ Country='NOR' AND Name IN ('Oslo', 'Bergen') OR
Country='ITA' AND Name IN ('Napoli', 'Venezia');
ID Name Country Population
175 Antwerpen BEL 446525
-2808 Bergen NOR 230948
+176 Gent BEL 224180
3068 Berlin DEU 3386667
3087 Bonn DEU 301048
-2918 Braga PRT 90535
-176 Gent BEL 224180
3242 Lahti FIN 96921
-3580 Moscow RUS 8389200
+2974 Paris FRA 2125246
1466 Napoli ITA 1002619
+1474 Venezia ITA 277305
+2808 Bergen NOR 230948
2807 Oslo NOR 508726
-2974 Paris FRA 2125246
+2928 Warszawa POL 1615369
+2931 Wroclaw POL 636765
+2918 Braga PRT 90535
2915 Porto PRT 273060
+3580 Moscow RUS 8389200
3581 St Petersburg RUS 4694000
3048 Stockholm SWE 750348
3051 Uppsala SWE 189569
-1474 Venezia ITA 277305
-2928 Warszawa POL 1615369
-2931 Wroclaw POL 636765
explain select * from City
where
Country='FIN' AND Name IN ('Lahti','Imatra') OR
@@ -1936,7 +1936,7 @@ 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 Name 35 NULL 20 Using index condition; Using where
+1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where
DROP DATABASE world;
set session optimizer_switch='index_merge_sort_intersection=default';
set global innodb_stats_persistent= @innodb_stats_persistent_save;
View Lorry Controller Status