diff options
author | Igor Babaev <igor@askmonty.org> | 2010-12-09 11:06:32 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2010-12-09 11:06:32 -0800 |
commit | afcefa97d9f814a20d97a1f8d2995bcd1633e1f2 (patch) | |
tree | bedf7e385fe6726ae8e17c0a13bd9a440a03b374 /mysql-test/r/range_vs_index_merge_innodb.result | |
parent | f960f2334dab6bef702a6f36e90dcdf6835588b6 (diff) | |
download | mariadb-git-afcefa97d9f814a20d97a1f8d2995bcd1633e1f2.tar.gz |
Addemdum for bug #685952.
Also:
Changed the value of TIME_FOR_COMPARE_ROWID to make it the same as for MWL 21.
Changed some queries in range_vs_index_merge.test to make them generate
the same plans as earlier.
Diffstat (limited to 'mysql-test/r/range_vs_index_merge_innodb.result')
-rw-r--r-- | mysql-test/r/range_vs_index_merge_innodb.result | 190 |
1 files changed, 48 insertions, 142 deletions
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index ae831aa10b5..75737de8942 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -72,9 +72,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Population Population 4 NULL 458 Using where EXPLAIN SELECT * FROM City -WHERE (Population > 101000 AND Population < 103000); +WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population Population 4 NULL 80 Using where +1 SIMPLE City range Population Population 4 NULL 38 Using where EXPLAIN SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')); @@ -89,9 +89,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) -AND (Population > 101000 AND Population < 103000); +AND (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Population 4 NULL 80 Using where +1 SIMPLE City range Population,Country,Name Population 4 NULL 38 Using where SELECT * FROM City USE INDEX () WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) AND (Population > 101000 AND Population < 115000); @@ -154,24 +154,20 @@ ID Name Country Population 4032 Cambridge USA 101355 SELECT * FROM City USE INDEX () WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) -AND (Population > 101000 AND Population < 103000); +AND (Population > 101000 AND Population < 102000); ID Name Country Population 637 Mit Ghamr EGY 101801 707 Marbella ESP 101144 -3411 Ceyhan TUR 102412 3792 Tartu EST 101246 -4027 Cape Coral USA 102286 4032 Cambridge USA 101355 SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')) -AND (Population > 101000 AND Population < 103000); +AND (Population > 101000 AND Population < 102000); ID Name Country Population 707 Marbella ESP 101144 3792 Tartu EST 101246 4032 Cambridge USA 101355 637 Mit Ghamr EGY 101801 -4027 Cape Coral USA 102286 -3411 Ceyhan TUR 102412 EXPLAIN SELECT * FROM City WHERE (Name < 'Ac'); id select_type table type possible_keys key key_len ref rows Extra @@ -329,17 +325,17 @@ ID Name Country Population 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 EXPLAIN -SELECT * FROM City WHERE (ID < 50) OR (ID BETWEEN 100 AND 110); +SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 60 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using where EXPLAIN -SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 300 AND 600); +SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1142 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 199 Using where EXPLAIN -SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1800); +SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 2950 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 2006 Using where EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; id select_type table type possible_keys key key_len ref rows Extra @@ -354,27 +350,27 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 133 Using where EXPLAIN SELECT * FROM City -WHERE ((ID < 50) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) +WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 60 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using where EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 900 AND 1800) AND +OR ((ID BETWEEN 900 AND 1500) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,Population 35,3,4 NULL 188 Using sort_union(Name,Country,Population); Using where EXPLAIN SELECT * FROM City -WHERE ((ID < 600) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 300 AND 600) AND +WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) +OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 1242 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 199 Using where SELECT * FROM City USE INDEX () -WHERE ((ID < 50) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) +WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population @@ -383,15 +379,10 @@ ID Name Country Population 3 Herat AFG 186800 4 Mazar-e-Sharif AFG 127800 7 Haag NLD 440900 -16 Haarlem NLD 148772 -25 Haarlemmermeer NLD 110722 -31 Heerlen NLD 95052 -33 Willemstad ANT 2345 -34 Tirana ALB 270000 100 Paraná ARG 207041 102 Posadas ARG 201273 SELECT * FROM City -WHERE ((ID < 50) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) +WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population @@ -400,16 +391,11 @@ ID Name Country Population 3 Herat AFG 186800 4 Mazar-e-Sharif AFG 127800 7 Haag NLD 440900 -16 Haarlem NLD 148772 -25 Haarlemmermeer NLD 110722 -31 Heerlen NLD 95052 -33 Willemstad ANT 2345 -34 Tirana ALB 270000 100 Paraná ARG 207041 102 Posadas ARG 201273 SELECT * FROM City USE INDEX() WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 900 AND 1800) AND +OR ((ID BETWEEN 900 AND 1500) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population 1 Kabul AFG 1780000 @@ -464,13 +450,9 @@ ID Name Country Population 1468 Palermo ITA 683794 1478 Padova ITA 211391 1484 Parma ITA 168717 -1530 Kingston JAM 103962 -1747 Toda JPN 103969 -1748 Tajimi JPN 103171 -1785 Ibb YEM 103300 SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 900 AND 1800) AND +OR ((ID BETWEEN 900 AND 1500) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population 1 Kabul AFG 1780000 @@ -525,13 +507,9 @@ ID Name Country Population 1468 Palermo ITA 683794 1478 Padova ITA 211391 1484 Parma ITA 168717 -1530 Kingston JAM 103962 -1747 Toda JPN 103969 -1748 Tajimi JPN 103171 -1785 Ibb YEM 103300 SELECT * FROM City USE INDEX () WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 300 AND 600) AND +OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population 1 Kabul AFG 1780000 @@ -556,25 +534,15 @@ ID Name Country Population 66 Sharja ARE 320095 67 al-Ayn ARE 225970 68 Ajman ARE 114395 +100 Paraná ARG 207041 129 Oranjestad ABW 29034 +167 Jamalpur BGD 103556 +168 Pabna BGD 103277 +189 Parakou BEN 103577 191 Hamilton BMU 1200 -339 Passo Fundo BRA 166343 -364 Parnaíba BRA 129756 -372 Paranaguá BRA 126076 -379 Palmas BRA 121919 -386 Patos de Minas BRA 119262 -411 Guaratinguetá BRA 103433 -412 Cachoeirinha BRA 103240 -413 Codó BRA 103153 -424 Passos BRA 98570 -430 Paulo Afonso BRA 97291 -435 Parnamirim BRA 96210 -448 Patos BRA 90519 -451 Palhoça BRA 89465 -517 Oldham GBR 103931 SELECT * FROM City WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) -OR ((ID BETWEEN 300 AND 600) AND +OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); ID Name Country Population 1 Kabul AFG 1780000 @@ -599,22 +567,12 @@ ID Name Country Population 66 Sharja ARE 320095 67 al-Ayn ARE 225970 68 Ajman ARE 114395 +100 Paraná ARG 207041 129 Oranjestad ABW 29034 +167 Jamalpur BGD 103556 +168 Pabna BGD 103277 +189 Parakou BEN 103577 191 Hamilton BMU 1200 -339 Passo Fundo BRA 166343 -364 Parnaíba BRA 129756 -372 Paranaguá BRA 126076 -379 Palmas BRA 121919 -386 Patos de Minas BRA 119262 -411 Guaratinguetá BRA 103433 -412 Cachoeirinha BRA 103240 -413 Codó BRA 103153 -424 Passos BRA 98570 -430 Paulo Afonso BRA 97291 -435 Parnamirim BRA 96210 -448 Patos BRA 90519 -451 Palhoça BRA 89465 -517 Oldham GBR 103931 EXPLAIN SELECT * FROM City WHERE Population > 101000 AND Population < 102000; id select_type table type possible_keys key key_len ref rows Extra @@ -735,6 +693,10 @@ SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City ref Country,CountryPopulation Country 3 const 274 Using where EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using where +EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -743,9 +705,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='USA'; +AND Country='FIN'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 274 Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -780,72 +742,22 @@ ID Name Country Population 4032 Cambridge USA 101355 SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='USA'; +AND Country='FIN'; ID Name Country Population -3797 Philadelphia USA 1517550 -3798 Phoenix USA 1321045 -3820 Portland USA 529121 -3844 Pittsburgh USA 334563 -3870 Plano USA 222030 -3912 Providence USA 173618 -3930 Pomona USA 149473 -3932 Paterson USA 149222 -3943 Pasadena USA 141674 -3951 Pembroke Pines USA 137427 -3953 Pasadena USA 133936 -3967 Paradise USA 124682 -3986 Palmdale USA 116670 -3996 Peoria USA 112936 -4007 Peoria USA 108364 -4016 Provo USA 105166 -4023 Gary USA 102746 -4024 Berkeley USA 102743 -4025 Santa Clara USA 102361 -4026 Green Bay USA 102313 -4027 Cape Coral USA 102286 -4028 Arvada USA 102153 -4029 Pueblo USA 102121 -4030 Sandy USA 101853 -4031 Athens-Clarke County USA 101489 -4032 Cambridge USA 101355 -4035 Portsmouth USA 100565 SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='USA'; +AND Country='FIN'; ID Name Country Population -3797 Philadelphia USA 1517550 -3798 Phoenix USA 1321045 -3820 Portland USA 529121 -3844 Pittsburgh USA 334563 -3870 Plano USA 222030 -3912 Providence USA 173618 -3930 Pomona USA 149473 -3932 Paterson USA 149222 -3943 Pasadena USA 141674 -3951 Pembroke Pines USA 137427 -3953 Pasadena USA 133936 -3967 Paradise USA 124682 -3986 Palmdale USA 116670 -3996 Peoria USA 112936 -4007 Peoria USA 108364 -4016 Provo USA 105166 -4023 Gary USA 102746 -4024 Berkeley USA 102743 -4025 Santa Clara USA 102361 -4026 Green Bay USA 102313 -4027 Cape Coral USA 102286 -4028 Arvada USA 102153 -4029 Pueblo USA 102121 -4030 Sandy USA 101853 -4031 Athens-Clarke County USA 101489 -4032 Cambridge USA 101355 -4035 Portsmouth USA 100565 CREATE INDEX CountryName ON City(Country,Name); EXPLAIN SELECT * FROM City WHERE Country='USA'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 274 Using where EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using where +EXPLAIN SELECT * FROM City WHERE Country='BRA'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 250 Using where @@ -902,10 +814,10 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 110000) OR -ID BETWEEN 3500 AND 3800) AND Country='USA' +ID BETWEEN 3500 AND 3800) AND Country='FIN' AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 274 Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' @@ -940,20 +852,14 @@ ID Name Country Population 4032 Cambridge USA 101355 SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR -ID BETWEEN 3790 AND 3800) AND Country='USA' +ID BETWEEN 3790 AND 3800) AND Country='FIN' AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); ID Name Country Population -4030 Sandy USA 101853 -4031 Athens-Clarke County USA 101489 -4032 Cambridge USA 101355 SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) OR -ID BETWEEN 3790 AND 3800) AND Country='USA' +ID BETWEEN 3790 AND 3800) AND Country='FIN' AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035); ID Name Country Population -4030 Sandy USA 101853 -4031 Athens-Clarke County USA 101489 -4032 Cambridge USA 101355 EXPLAIN SELECT * FROM City WHERE ((Population > 101000 and Population < 102000) OR |