diff options
| -rw-r--r-- | mysql-test/include/index_merge1.inc | 2 | ||||
| -rw-r--r-- | mysql-test/r/index_merge_myisam.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/range_vs_index_merge.result | 186 | ||||
| -rw-r--r-- | mysql-test/r/range_vs_index_merge_innodb.result | 190 | ||||
| -rwxr-xr-x | mysql-test/t/range_vs_index_merge.test | 54 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 2 | ||||
| -rw-r--r-- | sql/opt_range.cc | 5 |
7 files changed, 129 insertions, 314 deletions
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index b3e61802dcf..5cd7bec1895 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -194,7 +194,7 @@ alter table t2 add index i321(key3, key2, key1); explain select key3 from t2 where key1 = 100 or key2 = 100; # index_merge vs 'index', 'index' is better. -explain select key3 from t2 where key1 <100 or key2 < 100; +explain select key3 from t2 where key1 < 500 or key2 < 500; # index_merge vs 'all', index_merge is better. explain select key7 from t2 where key1 <100 or key2 < 100; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index db0b4abb7d5..dd65b56014f 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -203,12 +203,12 @@ alter table t2 add index i321(key3, key2, key1); explain select key3 from t2 where key1 = 100 or key2 = 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using sort_union(i1_3,i2_3); Using where -explain select key3 from t2 where key1 <100 or key2 < 100; +explain select key3 from t2 where key1 < 500 or key2 < 500; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index explain select key7 from t2 where key1 <100 or key2 < 100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL 1024 Using where +1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 188 Using sort_union(i1_3,i2_3); Using where create table t4 ( key1a int not null, key1b int not null, diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index dccb2e3b836..5ad4a5b1571 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -71,9 +71,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Population Population 4 NULL 459 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 81 Using where +1 SIMPLE City range Population Population 4 NULL 39 Using where EXPLAIN SELECT * FROM City WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F')); @@ -88,9 +88,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 81 Using where +1 SIMPLE City range Population,Country,Name Population 4 NULL 39 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); @@ -153,24 +153,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 @@ -328,15 +324,15 @@ 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 61 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 21 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 501 Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 201 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 ALL PRIMARY NULL NULL NULL 4079 Using where EXPLAIN @@ -353,27 +349,27 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 72 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 61 Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 21 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 128 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 index_merge PRIMARY,Population,Country,Name Name,Country,Population 35,3,4 NULL 128 Using sort_union(Name,Country,Population); 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 @@ -382,15 +378,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 @@ -399,16 +390,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 @@ -463,13 +449,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 @@ -524,13 +506,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 @@ -555,25 +533,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 @@ -598,22 +566,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 @@ -734,6 +692,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 267 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 6 Using where +EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -742,9 +704,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 267 Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 6 Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -779,72 +741,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 267 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 CountryName 3 const 5 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 CountryName 3 const 221 Using where @@ -901,10 +813,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 267 Using where +1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 3 const 5 Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) OR ID BETWEEN 3790 AND 3800) AND Country='USA' @@ -939,20 +851,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 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 diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index a6dc21bf05a..19ecb45c531 100755 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -68,7 +68,7 @@ SELECT * FROM City EXPLAIN SELECT * FROM City - WHERE (Population > 101000 AND Population < 103000); + WHERE (Population > 101000 AND Population < 102000); EXPLAIN SELECT * FROM City @@ -88,7 +88,7 @@ SELECT * FROM City 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); # The following 4 queries check that the plans # for the previous 2 plans are valid @@ -103,11 +103,11 @@ SELECT * FROM City 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); 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); # The output of the next 7 commands tells us about selectivities # of the conditions utilized in 4 queries following after them @@ -197,11 +197,11 @@ SELECT * FROM City # of the conditions utilized in 3 queries following after them 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); 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); 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); EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; EXPLAIN @@ -219,20 +219,20 @@ SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; 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))); 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))); 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))); @@ -240,33 +240,33 @@ SELECT * FROM City # for the previous 3 plans are valid 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))); 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))); 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))); 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))); 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))); 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))); @@ -353,7 +353,7 @@ ANALYZE TABLE City; --enable_result_log --enable_query_log -# The output of the next 4 commands tells us about selectivities +# The output of the next 5 commands tells us about selectivities # of the conditions utilized in 2 queries following after them EXPLAIN @@ -364,6 +364,8 @@ EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 103000); EXPLAIN SELECT * FROM City WHERE Country='USA'; +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; # The pattern of the WHERE condition used in the following 3 queries is # (range(key1_p2) OR (range(key2)) AND key1_p1=c @@ -380,7 +382,7 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') - AND Country='USA'; + AND Country='FIN'; # The following 4 queries check that the plans # for the previous 2 plans are valid @@ -395,11 +397,11 @@ SELECT * FROM City USE INDEX () SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') - AND Country='USA'; + AND Country='FIN'; SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') - AND Country='USA'; + AND Country='FIN'; CREATE INDEX CountryName ON City(Country,Name); @@ -412,12 +414,14 @@ ANALYZE TABLE City; --enable_result_log --enable_query_log -# The output of the next 11 commands tells us about selectivities +# The output of the next 12 commands tells us about selectivities # of the conditions utilized in 3 queries following after them EXPLAIN SELECT * FROM City WHERE Country='USA'; EXPLAIN +SELECT * FROM City WHERE Country='FIN'; +EXPLAIN SELECT * FROM City WHERE Country='BRA'; EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; @@ -464,7 +468,7 @@ SELECT * FROM City 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); # The following 6 queries check that the plans @@ -492,12 +496,12 @@ SELECT * FROM City 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); 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); diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 7905a0f52e5..274b4d71cea 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -338,7 +338,7 @@ protected: Number of comparisons of table rowids equivalent to reading one row from a table. */ -#define TIME_FOR_COMPARE_ROWID (TIME_FOR_COMPARE*2) +#define TIME_FOR_COMPARE_ROWID (TIME_FOR_COMPARE*100) /* For sequential disk seeks the cost formula is: diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 6ca9ff5e823..2435492061e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1093,7 +1093,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, { bool must_be_ored= sel_trees_must_be_ored(param, *or_tree, tree, ored_keys); - if (must_be_ored == is_first_check_pass) + if (must_be_ored || !is_first_check_pass) { result_keys.clear_all(); result= *or_tree; @@ -1138,8 +1138,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, *or_tree= result; if (is_first_check_pass) return 0; - else - was_ored= TRUE; + was_ored= TRUE; } } if (was_ored) |