Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1953 insertions, 0 deletions

diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result
new file mode 100644
index 00000000..79a670ae
--- /dev/null
+++ b/mysql-test/main/range_vs_index_merge_innodb.result
@@ -0,0 +1,1953 @@
+SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
+set @innodb_stats_persistent_save= @@innodb_stats_persistent;
+set @innodb_stats_persistent_sample_pages_save=
+@@innodb_stats_persistent_sample_pages;
+set global innodb_stats_persistent= 1;
+set global innodb_stats_persistent_sample_pages=100;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+SELECT COUNT(*) FROM Country;
+COUNT(*)
+239
+SELECT COUNT(*) FROM City;
+COUNT(*)
+4079
+SELECT COUNT(*) FROM CountryLanguage;
+COUNT(*)
+984
+CREATE INDEX Name ON City(Name);
+set session optimizer_switch='index_merge_sort_intersection=off';
+EXPLAIN
+SELECT * FROM City
+WHERE (Population >= 100000 OR Name LIKE 'P%' OR Population < 100000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	ALL	Population,Name	NULL	NULL	NULL	4079	Using where
+EXPLAIN
+SELECT * FROM City
+WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR
+(Population < 100000 OR Name Like 'T%') AND Country='ARG';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Country	3	NULL	106	Using index condition; Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Population < 200000 AND Name LIKE 'P%' AND
+(Population > 300000 OR Name LIKE 'T%') AND
+(Population < 100000 OR Name LIKE 'Pa%');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name	35	NULL	236	Using index condition; Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Population > 100000 AND Name LIKE 'Aba%' OR
+Country IN ('CAN', 'ARG') AND  ID BETWEEN 120 AND 130 OR
+Country <= 'ALB' AND Name LIKE 'L%' OR
+ID BETWEEN 3807 AND 3810;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	35,3,4	NULL	32	Using sort_union(Name,Country,PRIMARY); Using where
+EXPLAIN 
+SELECT * FROM City
+WHERE (Population > 101000 AND Population < 115000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	459	Using index condition
+EXPLAIN 
+SELECT * FROM City
+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	39	Using index condition
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Country,Name	Name,Country	35,3	NULL	215	Using sort_union(Name,Country); Using where
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
+AND (Population > 101000 AND Population < 115000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Country,Name	Name,Country	35,3	NULL	215	Using sort_union(Name,Country); Using where
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
+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	39	Using index condition; 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);
+ID	Name	Country	Population
+403	Catanduva	BRA	107761
+412	Cachoeirinha	BRA	103240
+636	Bilbays	EGY	113608
+637	Mit Ghamr	EGY	101801
+701	Tarragona	ESP	113016
+702	Lleida (Lérida)	ESP	112207
+703	Jaén	ESP	109247
+704	Ourense (Orense)	ESP	109120
+705	Mataró	ESP	104095
+706	Algeciras	ESP	103106
+707	Marbella	ESP	101144
+759	Gonder	ETH	112249
+869	Cabuyao	PHL	106630
+870	Calapan	PHL	105910
+873	Cauayan	PHL	103952
+1844	Cape Breton	CAN	114733
+1847	Cambridge	CAN	109186
+2908	Cajamarca	PER	108009
+3003	Caen	FRA	113987
+3411	Ceyhan	TUR	102412
+3571	Calabozo	VEN	107146
+3786	Cam Ranh	VNM	114041
+3792	Tartu	EST	101246
+4002	Carrollton	USA	109576
+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 < 115000);
+ID	Name	Country	Population
+403	Catanduva	BRA	107761
+412	Cachoeirinha	BRA	103240
+636	Bilbays	EGY	113608
+637	Mit Ghamr	EGY	101801
+701	Tarragona	ESP	113016
+702	Lleida (Lérida)	ESP	112207
+703	Jaén	ESP	109247
+704	Ourense (Orense)	ESP	109120
+705	Mataró	ESP	104095
+706	Algeciras	ESP	103106
+707	Marbella	ESP	101144
+759	Gonder	ETH	112249
+869	Cabuyao	PHL	106630
+870	Calapan	PHL	105910
+873	Cauayan	PHL	103952
+1844	Cape Breton	CAN	114733
+1847	Cambridge	CAN	109186
+2908	Cajamarca	PER	108009
+3003	Caen	FRA	113987
+3411	Ceyhan	TUR	102412
+3571	Calabozo	VEN	107146
+3786	Cam Ranh	VNM	114041
+3792	Tartu	EST	101246
+4002	Carrollton	USA	109576
+4027	Cape Coral	USA	102286
+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 < 102000);
+ID	Name	Country	Population
+637	Mit Ghamr	EGY	101801
+707	Marbella	ESP	101144
+3792	Tartu	EST	101246
+4032	Cambridge	USA	101355
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
+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
+EXPLAIN
+SELECT  * FROM City WHERE (Name < 'Ac');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	23	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Name < 'Bb');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	374	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Country > 'A' AND Country < 'B');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country	Country	3	NULL	107	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Name BETWEEN 'P' AND 'Pb');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	71	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Name BETWEEN 'P' AND 'S');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	385	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Population > 101000 AND Population < 110000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	328	Using index condition
+EXPLAIN
+SELECT  * FROM City WHERE (Population > 103000 AND Population < 104000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	37	Using index condition
+EXPLAIN
+SELECT  * FROM City 
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Name	35	NULL	94	Using index condition; Using where
+EXPLAIN
+SELECT  * FROM City
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Country,Name	Name,Population	35,4	NULL	60	Using sort_union(Name,Population); Using where
+EXPLAIN
+SELECT  * FROM City
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Country,Name	Country,Name	3,35	NULL	178	Using sort_union(Country,Name); Using where
+EXPLAIN
+SELECT  * FROM City
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Country,Name	Country,Population	3,4	NULL	144	Using sort_union(Country,Population); Using where
+SELECT  * FROM City USE INDEX ()
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+ID	Name	Country	Population
+65	Abu Dhabi	ARE	398695
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+750	Paarl	ZAF	105768
+2865	Pak Pattan	PAK	107800
+SELECT  * FROM City 
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+ID	Name	Country	Population
+65	Abu Dhabi	ARE	398695
+750	Paarl	ZAF	105768
+168	Pabna	BGD	103277
+2865	Pak Pattan	PAK	107800
+189	Parakou	BEN	103577
+SELECT  * FROM City USE INDEX ()
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+ID	Name	Country	Population
+65	Abu Dhabi	ARE	398695
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+1003	Pemalang	IDN	103500
+2663	Río Bravo	MEX	103901
+SELECT  * FROM City
+WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+ID	Name	Country	Population
+65	Abu Dhabi	ARE	398695
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+1003	Pemalang	IDN	103500
+2663	Río Bravo	MEX	103901
+SELECT  * FROM City USE INDEX ()
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+ID	Name	Country	Population
+55	Andorra la Vella	AND	21189
+65	Abu Dhabi	ARE	398695
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+75	Almirante Brown	ARG	538918
+85	Avellaneda	ARG	353046
+96	Bahía Blanca	ARG	239810
+134	Adelaide	AUS	978100
+144	Baku	AZE	1787800
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+750	Paarl	ZAF	105768
+2865	Pak Pattan	PAK	107800
+SELECT  * FROM City
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
+ID	Name	Country	Population
+55	Andorra la Vella	AND	21189
+65	Abu Dhabi	ARE	398695
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+75	Almirante Brown	ARG	538918
+85	Avellaneda	ARG	353046
+96	Bahía Blanca	ARG	239810
+134	Adelaide	AUS	978100
+144	Baku	AZE	1787800
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+750	Paarl	ZAF	105768
+2865	Pak Pattan	PAK	107800
+SELECT  * FROM City USE INDEX ()
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+ID	Name	Country	Population
+55	Andorra la Vella	AND	21189
+65	Abu Dhabi	ARE	398695
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+75	Almirante Brown	ARG	538918
+85	Avellaneda	ARG	353046
+96	Bahía Blanca	ARG	239810
+134	Adelaide	AUS	978100
+144	Baku	AZE	1787800
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+1003	Pemalang	IDN	103500
+2663	Río Bravo	MEX	103901
+SELECT  * FROM City
+WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR
+(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000));
+ID	Name	Country	Population
+55	Andorra la Vella	AND	21189
+65	Abu Dhabi	ARE	398695
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+75	Almirante Brown	ARG	538918
+85	Avellaneda	ARG	353046
+96	Bahía Blanca	ARG	239810
+134	Adelaide	AUS	978100
+144	Baku	AZE	1787800
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+1003	Pemalang	IDN	103500
+2663	Río Bravo	MEX	103901
+EXPLAIN
+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	20	Using where
+EXPLAIN
+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	200	Using where
+EXPLAIN
+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	1200	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
+1	SIMPLE	City	range	Country	Country	3	NULL	20	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	395	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	133	Using index condition
+EXPLAIN
+SELECT * FROM City
+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	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 1500) AND 
+(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 105000)));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Name,Country,PRIMARY	39,3,4	NULL	683	Using sort_union(Name,Country,PRIMARY); Using where
+EXPLAIN
+SELECT * FROM City
+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 > 103200 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	200	Using where
+SELECT * FROM City USE INDEX ()
+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
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+100	Paraná	ARG	207041
+102	Posadas	ARG	201273
+SELECT * FROM City
+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
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+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 1500) AND 
+(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
+ID	Name	Country	Population
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+16	Haarlem	NLD	148772
+25	Haarlemmermeer	NLD	110722
+33	Willemstad	ANT	2345
+34	Tirana	ALB	270000
+55	Andorra la Vella	AND	21189
+56	Luanda	AGO	2022000
+57	Huambo	AGO	163100
+58	Lobito	AGO	130000
+59	Benguela	AGO	128300
+60	Namibe	AGO	118200
+61	South Hill	AIA	961
+62	The Valley	AIA	595
+64	Dubai	ARE	669181
+65	Abu Dhabi	ARE	398695
+66	Sharja	ARE	320095
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+129	Oranjestad	ABW	29034
+191	Hamilton	BMU	1200
+528	Hartlepool	GBR	92000
+529	Halifax	GBR	91069
+914	Sekondi-Takoradi	GHA	103653
+943	Palembang	IDN	1222764
+950	Padang	IDN	534474
+983	Palu	IDN	142800
+984	Pasuruan	IDN	134019
+991	Pangkal Pinang	IDN	124000
+1003	Pemalang	IDN	103500
+1004	Klaten	IDN	103300
+1007	Palangka Raya	IDN	99693
+1020	Padang Sidempuan	IDN	91200
+1045	Patna	IND	917243
+1114	Panihati	IND	275990
+1129	Patiala	IND	238368
+1142	Panipat	IND	215218
+1159	Parbhani	IND	190255
+1231	Pali	IND	136842
+1263	Pathankot	IND	123930
+1265	Palghat (Palakkad)	IND	123289
+1293	Pallavaram	IND	111866
+1319	Tellicherry (Thalassery)	IND	103579
+1339	Palayankottai	IND	97662
+1345	Patan	IND	96109
+1436	Marv Dasht	IRN	103579
+1468	Palermo	ITA	683794
+1478	Padova	ITA	211391
+1484	Parma	ITA	168717
+SELECT * FROM City
+WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
+OR ((ID BETWEEN 900 AND 1500) AND 
+(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
+ID	Name	Country	Population
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+16	Haarlem	NLD	148772
+25	Haarlemmermeer	NLD	110722
+33	Willemstad	ANT	2345
+34	Tirana	ALB	270000
+55	Andorra la Vella	AND	21189
+56	Luanda	AGO	2022000
+57	Huambo	AGO	163100
+58	Lobito	AGO	130000
+59	Benguela	AGO	128300
+60	Namibe	AGO	118200
+61	South Hill	AIA	961
+62	The Valley	AIA	595
+64	Dubai	ARE	669181
+65	Abu Dhabi	ARE	398695
+66	Sharja	ARE	320095
+67	al-Ayn	ARE	225970
+68	Ajman	ARE	114395
+129	Oranjestad	ABW	29034
+191	Hamilton	BMU	1200
+528	Hartlepool	GBR	92000
+529	Halifax	GBR	91069
+914	Sekondi-Takoradi	GHA	103653
+943	Palembang	IDN	1222764
+950	Padang	IDN	534474
+983	Palu	IDN	142800
+984	Pasuruan	IDN	134019
+991	Pangkal Pinang	IDN	124000
+1003	Pemalang	IDN	103500
+1004	Klaten	IDN	103300
+1007	Palangka Raya	IDN	99693
+1020	Padang Sidempuan	IDN	91200
+1045	Patna	IND	917243
+1114	Panihati	IND	275990
+1129	Patiala	IND	238368
+1142	Panipat	IND	215218
+1159	Parbhani	IND	190255
+1231	Pali	IND	136842
+1263	Pathankot	IND	123930
+1265	Palghat (Palakkad)	IND	123289
+1293	Pallavaram	IND	111866
+1319	Tellicherry (Thalassery)	IND	103579
+1339	Palayankottai	IND	97662
+1345	Patan	IND	96109
+1436	Marv Dasht	IRN	103579
+1468	Palermo	ITA	683794
+1478	Padova	ITA	211391
+1484	Parma	ITA	168717
+SELECT * FROM City USE INDEX ()
+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	Name	Country	Population
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+16	Haarlem	NLD	148772
+25	Haarlemmermeer	NLD	110722
+33	Willemstad	ANT	2345
+34	Tirana	ALB	270000
+55	Andorra la Vella	AND	21189
+56	Luanda	AGO	2022000
+57	Huambo	AGO	163100
+58	Lobito	AGO	130000
+59	Benguela	AGO	128300
+60	Namibe	AGO	118200
+61	South Hill	AIA	961
+62	The Valley	AIA	595
+64	Dubai	ARE	669181
+65	Abu Dhabi	ARE	398695
+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
+SELECT * FROM City
+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	Name	Country	Population
+1	Kabul	AFG	1780000
+2	Qandahar	AFG	237500
+3	Herat	AFG	186800
+4	Mazar-e-Sharif	AFG	127800
+7	Haag	NLD	440900
+16	Haarlem	NLD	148772
+25	Haarlemmermeer	NLD	110722
+33	Willemstad	ANT	2345
+34	Tirana	ALB	270000
+55	Andorra la Vella	AND	21189
+56	Luanda	AGO	2022000
+57	Huambo	AGO	163100
+58	Lobito	AGO	130000
+59	Benguela	AGO	128300
+60	Namibe	AGO	118200
+61	South Hill	AIA	961
+62	The Valley	AIA	595
+64	Dubai	ARE	669181
+65	Abu Dhabi	ARE	398695
+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
+EXPLAIN 
+SELECT * FROM City 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	39	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE Population > 101000 AND Population < 110000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	328	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE Country < 'C';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country	Country	3	NULL	446	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE Country < 'AGO';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country	Country	3	NULL	5	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	385	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	71	Using index condition
+EXPLAIN 
+SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	401	Using where
+EXPLAIN 
+SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	11	Using where
+EXPLAIN 
+SELECT * FROM City WHERE Name LIKE 'P%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	236	Using index condition
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 102000) AND
+(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR
+((ID BETWEEN 3400 AND 3800) AND 
+(Country < 'AGO' OR Name LIKE 'Pa%'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Population,PRIMARY	4,4	NULL	440	Using sort_union(Population,PRIMARY); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 110000) AND
+(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR
+((ID BETWEEN 3790 AND 3800) AND 
+(Country < 'C' OR Name LIKE 'P%'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name	Country,Name,PRIMARY	3,35,4	NULL	87	Using sort_union(Country,Name,PRIMARY); Using where
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 102000) AND
+(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR
+((ID BETWEEN 3400 AND 3800) AND 
+(Country < 'AGO' OR Name LIKE 'Pa%'));
+ID	Name	Country	Population
+169	Naogaon	BGD	101266
+205	Francistown	BWA	101805
+417	Itaituba	BRA	101320
+418	Araras	BRA	101046
+751	Potchefstroom	ZAF	101817
+2909	Puno	PER	101578
+3463	Pavlograd	UKR	127000
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 102000) AND
+(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR
+((ID BETWEEN 3400 AND 3800) AND 
+(Country < 'AGO' OR Name LIKE 'Pa%'));
+ID	Name	Country	Population
+169	Naogaon	BGD	101266
+205	Francistown	BWA	101805
+417	Itaituba	BRA	101320
+418	Araras	BRA	101046
+751	Potchefstroom	ZAF	101817
+2909	Puno	PER	101578
+3463	Pavlograd	UKR	127000
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 110000) AND
+(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR
+((ID BETWEEN 3790 AND 3800) AND 
+(Country < 'C' OR Name LIKE 'P%'));
+ID	Name	Country	Population
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+750	Paarl	ZAF	105768
+2865	Pak Pattan	PAK	107800
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 110000) AND
+(Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR
+((ID BETWEEN 3790 AND 3800) AND 
+(Country < 'C' OR Name LIKE 'P%'));
+ID	Name	Country	Population
+168	Pabna	BGD	103277
+189	Parakou	BEN	103577
+750	Paarl	ZAF	105768
+2865	Pak Pattan	PAK	107800
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+CREATE INDEX CountryPopulation ON City(Country,Population);
+EXPLAIN
+SELECT * FROM City WHERE Name LIKE 'Pas%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	8	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE Name LIKE 'P%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	236	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	81	Using index condition
+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	Country	3	const	274	Using index condition
+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 index condition
+EXPLAIN
+SELECT * FROM City 
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+AND Country='USA';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Country,Name,CountryPopulation	CountryPopulation,Name	7,35	NULL	18	Using sort_union(CountryPopulation,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
+AND Country='EST';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	ref	Population,Country,Name,CountryPopulation	Country	3	const	2	Using index condition; Using where
+SELECT * FROM City 
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+AND Country='USA';
+ID	Name	Country	Population
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+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
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+AND Country='USA';
+ID	Name	Country	Population
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+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
+SELECT * FROM City 
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
+AND Country='FIN';
+ID	Name	Country	Population
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
+AND Country='FIN';
+ID	Name	Country	Population
+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 index condition
+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 index condition
+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 index condition
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	11	Using where
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	11	Using where
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	5	Using where
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	301	Using where
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	80	Using where
+EXPLAIN
+SELECT * FROM City WHERE ID BETWEEN 250 and 260 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY	PRIMARY	4	NULL	11	Using where
+EXPLAIN
+SELECT * FROM City 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	39	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population	Population	4	NULL	81	Using index condition
+EXPLAIN
+SELECT * FROM City WHERE Name LIKE 'Pa%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Name	Name	35	NULL	71	Using index condition
+set @tmp_range_vs_index_merge=@@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name,CountryPopulation,CountryName	CountryPopulation,PRIMARY	7,4	NULL	14	Using sort_union(CountryPopulation,PRIMARY); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 103000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name,CountryPopulation,CountryName	CountryName,PRIMARY	38,4	NULL	10	Using sort_union(CountryName,PRIMARY); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 110000) OR
+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	7	Using index condition; Using where
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        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'
+        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 USE INDEX ()
+WHERE ((Population > 101000 AND Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032);
+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'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4028 AND 4032);
+ID	Name	Country	Population
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='FIN'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035);
+ID	Name	Country	Population
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='FIN'
+        AND (Name LIKE 'Pa%' OR ID BETWEEN 4025 AND 4035);
+ID	Name	Country	Population
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 and Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	PRIMARY,Population,Country,Name,CountryPopulation,CountryName	CountryPopulation,CountryName,PRIMARY	7,38,4	NULL	36	Using sort_union(CountryPopulation,CountryName,PRIMARY); Using where
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 and Population < 102000) OR
+ID BETWEEN 3790 AND 3800) AND Country='USA'
+        OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA';
+ID	Name	Country	Population
+250	Mauá	BRA	375055
+251	Carapicuíba	BRA	357552
+252	Olinda	BRA	354732
+253	Campina Grande	BRA	352497
+254	São José do Rio Preto	BRA	351944
+255	Caxias do Sul	BRA	349581
+256	Moji das Cruzes	BRA	339194
+257	Diadema	BRA	335078
+258	Aparecida de Goiânia	BRA	324662
+259	Piracicaba	BRA	319104
+260	Cariacica	BRA	319033
+285	Paulista	BRA	248473
+339	Passo Fundo	BRA	166343
+364	Parnaíba	BRA	129756
+372	Paranaguá	BRA	126076
+379	Palmas	BRA	121919
+386	Patos de Minas	BRA	119262
+424	Passos	BRA	98570
+430	Paulo Afonso	BRA	97291
+435	Parnamirim	BRA	96210
+448	Patos	BRA	90519
+451	Palhoça	BRA	89465
+3793	New York	USA	8008278
+3794	Los Angeles	USA	3694820
+3795	Chicago	USA	2896016
+3796	Houston	USA	1953631
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+3799	San Diego	USA	1223400
+3800	Dallas	USA	1188580
+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'
+        OR (Name LIKE 'Pa%' OR ID BETWEEN 250 AND 260) AND Country='BRA';
+ID	Name	Country	Population
+285	Paulista	BRA	248473
+339	Passo Fundo	BRA	166343
+364	Parnaíba	BRA	129756
+372	Paranaguá	BRA	126076
+379	Palmas	BRA	121919
+386	Patos de Minas	BRA	119262
+424	Passos	BRA	98570
+430	Paulo Afonso	BRA	97291
+435	Parnamirim	BRA	96210
+448	Patos	BRA	90519
+451	Palhoça	BRA	89465
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+250	Mauá	BRA	375055
+251	Carapicuíba	BRA	357552
+252	Olinda	BRA	354732
+253	Campina Grande	BRA	352497
+254	São José do Rio Preto	BRA	351944
+255	Caxias do Sul	BRA	349581
+256	Moji das Cruzes	BRA	339194
+257	Diadema	BRA	335078
+258	Aparecida de Goiânia	BRA	324662
+259	Piracicaba	BRA	319104
+260	Cariacica	BRA	319033
+3793	New York	USA	8008278
+3794	Los Angeles	USA	3694820
+3795	Chicago	USA	2896016
+3796	Houston	USA	1953631
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+3799	San Diego	USA	1223400
+3800	Dallas	USA	1188580
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country,Name,CountryPopulation,CountryName	CountryName	38	NULL	18	Using index condition
+EXPLAIN
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country,Name,CountryPopulation,CountryName	Name	35	NULL	1	Using index condition; Using where
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300);
+ID	Name	Country	Population
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300);
+ID	Name	Country	Population
+3797	Philadelphia	USA	1517550
+3798	Phoenix	USA	1321045
+SELECT * FROM City USE INDEX ()
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300);
+ID	Name	Country	Population
+3798	Phoenix	USA	1321045
+SELECT * FROM City
+WHERE ((Population > 101000 AND Population < 11000) OR
+ID BETWEEN 3500 AND 3800) AND Country='USA'
+        AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300);
+ID	Name	Country	Population
+3798	Phoenix	USA	1321045
+DROP INDEX Population ON City;
+DROP INDEX Name ON City;
+set optimizer_switch=@tmp_range_vs_index_merge;
+EXPLAIN
+SELECT * FROM City
+WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR
+Country='USA' AND Name LIKE 'Pa%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Country,CountryPopulation,CountryName	CountryPopulation,CountryName	7,38	NULL	8	Using sort_union(CountryPopulation,CountryName); Using where
+SELECT * FROM City USE INDEX()
+WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR
+Country='USA' AND Name LIKE 'Pa%';
+ID	Name	Country	Population
+3932	Paterson	USA	149222
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+3967	Paradise	USA	124682
+3986	Palmdale	USA	116670
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+SELECT * FROM City
+WHERE Country='USA' AND Population BETWEEN 101000 AND 102000 OR
+Country='USA' AND Name LIKE 'Pa%';
+ID	Name	Country	Population
+3932	Paterson	USA	149222
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+3967	Paradise	USA	124682
+3986	Palmdale	USA	116670
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+EXPLAIN
+SELECT * FROM City
+WHERE Country='USA' AND 
+(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Country,CountryPopulation,CountryName	CountryPopulation,CountryName	7,38	NULL	8	Using sort_union(CountryPopulation,CountryName); Using where
+SELECT * FROM City
+WHERE Country='USA' AND 
+(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%');
+ID	Name	Country	Population
+3932	Paterson	USA	149222
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+3967	Paradise	USA	124682
+3986	Palmdale	USA	116670
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+SELECT * FROM City
+WHERE Country='USA' AND 
+(Population BETWEEN 101000 AND 102000 OR Name LIKE 'Pa%');
+ID	Name	Country	Population
+3932	Paterson	USA	149222
+3943	Pasadena	USA	141674
+3953	Pasadena	USA	133936
+3967	Paradise	USA	124682
+3986	Palmdale	USA	116670
+4030	Sandy	USA	101853
+4031	Athens-Clarke County	USA	101489
+4032	Cambridge	USA	101355
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE INDEX CityName on City(Name);
+EXPLAIN SELECT Name, Country, Population FROM City WHERE
+(Name='Manila' AND Country='PHL') OR
+(Name='Addis Abeba' AND Country='ETH') OR          
+(Name='Jakarta' AND Country='IDN') OR  
+(Name='Bangalore' AND Country='IND') OR
+(Name='Teheran' AND Country='IRN') OR          
+(Name='Roma' AND Country='ITA') OR  
+(Name='Delhi' AND Country='IND') OR  
+(Name='Venezia' AND Country='ITA') OR  
+(Name='Tokyo' AND Country='JPN') OR
+(Name='Toronto' AND Country='CAN') OR  
+(Name='Peking' AND Country='CHN') OR  
+(Name='Lagos' AND Country='NGA') OR  
+(Name='Tijuana' AND Country='MEX') OR
+(Name='Rabat' AND Country='MAR') OR          
+(Name='Seoul' AND Country='KOR') OR  
+(Name='Vancouver' AND Country='CAN') OR  
+(Name='Kaunas' AND Country='LTU') OR  
+(Name='Paris' AND Country='FRA') OR
+(Name='Dakar' AND Country='SEN') OR          
+(Name='Basel' AND Country='CHE') OR  
+(Name='Praha' AND Country='CZE') OR  
+(Name='Ankara' AND Country='TUR') OR  
+(Name='Dresden' AND Country='DEU') OR
+(Name='Lugansk' AND Country='UKR') OR          
+(Name='Caracas' AND Country='VEN') OR  
+(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
+SELECT Name, Country, Population FROM City WHERE
+(Name='Manila' AND Country='PHL') OR
+(Name='Addis Abeba' AND Country='ETH') OR          
+(Name='Jakarta' AND Country='IDN') OR  
+(Name='Bangalore' AND Country='IND') OR
+(Name='Teheran' AND Country='IRN') OR          
+(Name='Roma' AND Country='ITA') OR  
+(Name='Delhi' AND Country='IND') OR  
+(Name='Venezia' AND Country='ITA') OR  
+(Name='Tokyo' AND Country='JPN') OR
+(Name='Toronto' AND Country='CAN') OR  
+(Name='Peking' AND Country='CHN') OR  
+(Name='Lagos' AND Country='NGA') OR  
+(Name='Tijuana' AND Country='MEX') OR
+(Name='Rabat' AND Country='MAR') OR          
+(Name='Seoul' AND Country='KOR') OR  
+(Name='Vancouver' AND Country='CAN') OR  
+(Name='Kaunas' AND Country='LTU') OR  
+(Name='Paris' AND Country='FRA') OR
+(Name='Dakar' AND Country='SEN') OR          
+(Name='Basel' AND Country='CHE') OR  
+(Name='Praha' AND Country='CZE') OR  
+(Name='Ankara' AND Country='TUR') OR  
+(Name='Dresden' AND Country='DEU') OR
+(Name='Lugansk' AND Country='UKR') OR          
+(Name='Caracas' AND Country='VEN') OR  
+(Name='Samara' AND Country='RUS') OR  
+(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
+Jakarta	IDN	9604900
+Kaunas	LTU	412639
+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
+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
+(Name='Addis Abeba' AND Country='ETH') OR          
+(Name='Jakarta' AND Country='IDN') OR  
+(Name='Bangalore' AND Country='IND') OR
+(Name='Teheran' AND Country='IRN') OR          
+(Name='Roma' AND Country='ITA') OR  
+(Name='Delhi' AND Country='IND') OR  
+(Name='Venezia' AND Country='ITA') OR  
+(Name='Tokyo' AND Country='JPN') OR
+(Name='Toronto' AND Country='CAN') OR  
+(Name='Peking' AND Country='CHN') OR  
+(Name='Lagos' AND Country='NGA') OR  
+(Name='Tijuana' AND Country='MEX') OR
+(Name='Rabat' AND Country='MAR') OR          
+(Name='Seoul' AND Country='KOR') OR  
+(Name='Vancouver' AND Country='CAN') OR  
+(Name='Kaunas' AND Country='LTU') OR  
+(Name='Paris' AND Country='FRA') OR
+(Name='Dakar' AND Country='SEN') OR          
+(Name='Basel' AND Country='CHE') OR  
+(Name='Praha' AND Country='CZE') OR  
+(Name='Ankara' AND Country='TUR') OR  
+(Name='Dresden' AND Country='DEU') OR
+(Name='Lugansk' AND Country='UKR') OR          
+(Name='Caracas' AND Country='VEN') OR  
+(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
+SELECT Name, Country, Population FROM City WHERE
+(Name='Manila' AND Country='PHL') OR
+(Name='Addis Abeba' AND Country='ETH') OR          
+(Name='Jakarta' AND Country='IDN') OR  
+(Name='Bangalore' AND Country='IND') OR
+(Name='Teheran' AND Country='IRN') OR          
+(Name='Roma' AND Country='ITA') OR  
+(Name='Delhi' AND Country='IND') OR  
+(Name='Venezia' AND Country='ITA') OR  
+(Name='Tokyo' AND Country='JPN') OR
+(Name='Toronto' AND Country='CAN') OR  
+(Name='Peking' AND Country='CHN') OR  
+(Name='Lagos' AND Country='NGA') OR  
+(Name='Tijuana' AND Country='MEX') OR
+(Name='Rabat' AND Country='MAR') OR          
+(Name='Seoul' AND Country='KOR') OR  
+(Name='Vancouver' AND Country='CAN') OR  
+(Name='Kaunas' AND Country='LTU') OR  
+(Name='Paris' AND Country='FRA') OR
+(Name='Dakar' AND Country='SEN') OR          
+(Name='Basel' AND Country='CHE') OR  
+(Name='Praha' AND Country='CZE') OR  
+(Name='Ankara' AND Country='TUR') OR  
+(Name='Dresden' AND Country='DEU') OR
+(Name='Lugansk' AND Country='UKR') OR          
+(Name='Caracas' AND Country='VEN') OR  
+(Name='Samara' AND Country='RUS') OR  
+(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
+Jakarta	IDN	9604900
+Kaunas	LTU	412639
+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
+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=@save_optimizer_switch;
+# 
+# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n
+# (LP bug #637962)
+# 
+DROP INDEX CountryPopulation ON City;
+DROP INDEX CountryName ON City;
+DROP INDEX CityName on City;
+CREATE INDEX Name ON City(Name);
+CREATE INDEX Population ON City(Population);
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Country,Name,Population	Name,Country	35,3	NULL	#	Using sort_union(Name,Country); Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000);
+ID	Name	Country	Population
+384	Cabo Frio	BRA	119503
+387	Camaragibe	BRA	118968
+403	Catanduva	BRA	107761
+412	Cachoeirinha	BRA	103240
+508	Watford	GBR	113080
+509	Ipswich	GBR	114000
+510	Slough	GBR	112000
+511	Exeter	GBR	111000
+512	Cheltenham	GBR	106000
+513	Gloucester	GBR	107000
+514	Saint Helens	GBR	106293
+515	Sutton Coldfield	GBR	106001
+516	York	GBR	104425
+517	Oldham	GBR	103931
+518	Basildon	GBR	100924
+519	Worthing	GBR	100000
+635	Mallawi	EGY	119283
+636	Bilbays	EGY	113608
+637	Mit Ghamr	EGY	101801
+638	al-Arish	EGY	100447
+701	Tarragona	ESP	113016
+702	Lleida (Lérida)	ESP	112207
+703	Jaén	ESP	109247
+704	Ourense (Orense)	ESP	109120
+705	Mataró	ESP	104095
+706	Algeciras	ESP	103106
+707	Marbella	ESP	101144
+759	Gonder	ETH	112249
+869	Cabuyao	PHL	106630
+870	Calapan	PHL	105910
+873	Cauayan	PHL	103952
+903	Serekunda	GMB	102600
+909	Sohumi	GEO	111700
+913	Tema	GHA	109975
+914	Sekondi-Takoradi	GHA	103653
+924	Villa Nueva	GTM	101295
+1844	Cape Breton	CAN	114733
+1847	Cambridge	CAN	109186
+2406	Herakleion	GRC	116178
+2407	Kallithea	GRC	114233
+2408	Larisa	GRC	113090
+2908	Cajamarca	PER	108009
+3002	Besançon	FRA	117733
+3003	Caen	FRA	113987
+3004	Orléans	FRA	113126
+3005	Mulhouse	FRA	110359
+3006	Rouen	FRA	106592
+3007	Boulogne-Billancourt	FRA	106367
+3008	Perpignan	FRA	105115
+3009	Nancy	FRA	103605
+3411	Ceyhan	TUR	102412
+3567	Carúpano	VEN	119639
+3568	Catia La Mar	VEN	117012
+3571	Calabozo	VEN	107146
+3786	Cam Ranh	VNM	114041
+3792	Tartu	EST	101246
+4002	Carrollton	USA	109576
+4027	Cape Coral	USA	102286
+4032	Cambridge	USA	101355
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	2
+Handler_read_last	0
+Handler_read_next	385
+Handler_read_prev	0
+Handler_read_retry	0
+Handler_read_rnd	377
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country,Name,Population	Population	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID	Name	Country	Population
+519	Worthing	GBR	100000
+638	al-Arish	EGY	100447
+518	Basildon	GBR	100924
+707	Marbella	ESP	101144
+3792	Tartu	EST	101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	59
+Handler_read_prev	0
+Handler_read_retry	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+set @tmp_mdev585=@@optimizer_use_condition_selectivity;
+set optimizer_use_condition_selectivity=1;
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country,Name,Population	Population	4	NULL	#	Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID	Name	Country	Population
+519	Worthing	GBR	100000
+638	al-Arish	EGY	100447
+518	Basildon	GBR	100924
+707	Marbella	ESP	101144
+3792	Tartu	EST	101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	59
+Handler_read_prev	0
+Handler_read_retry	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+set optimizer_use_condition_selectivity=@tmp_mdev585;
+set optimizer_switch='index_merge=off';
+EXPLAIN 
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Country,Name,Population	Population	4	NULL	#	Using index condition; Using where
+FLUSH STATUS;
+SELECT * FROM City
+WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+ID	Name	Country	Population
+519	Worthing	GBR	100000
+638	al-Arish	EGY	100447
+518	Basildon	GBR	100924
+707	Marbella	ESP	101144
+3792	Tartu	EST	101246
+SHOW STATUS LIKE 'Handler_read_%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	1
+Handler_read_last	0
+Handler_read_next	59
+Handler_read_prev	0
+Handler_read_retry	0
+Handler_read_rnd	0
+Handler_read_rnd_deleted	0
+Handler_read_rnd_next	0
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE world;
+use test;
+CREATE TABLE t1 (
+id int(10) unsigned NOT NULL auto_increment,
+account_id int(10) unsigned NOT NULL,
+first_name varchar(50) default NULL,
+middle_name varchar(50) default NULL,
+last_name  varchar(100) default NULL,
+home_address_1 varchar(150) default NULL,
+home_city varchar(75) default NULL,
+home_state char(2) default NULL,
+home_postal_code varchar(50) default NULL,
+home_county varchar(75) default NULL,
+home_country char(3) default NULL,
+work_address_1 varchar(150) default NULL,
+work_city varchar(75) default NULL,
+work_state char(2) default NULL,
+work_postal_code varchar(50) default NULL,
+work_county varchar(75) default NULL,
+work_country char(3) default NULL,
+login varchar(50) NOT NULL,
+PRIMARY KEY  (id),
+KEY login (login,account_id),
+KEY account_id (account_id),
+KEY user_home_country_indx (home_country),
+KEY user_work_country_indx (work_country),
+KEY user_home_state_indx (home_state),
+KEY user_work_state_indx (work_state),
+KEY user_home_city_indx (home_city),
+KEY user_work_city_indx (work_city),
+KEY user_first_name_indx (first_name),
+KEY user_last_name_indx (last_name)
+);
+insert into t1(account_id, login, home_state, work_state) values
+(1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'),
+(1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia'), (1, 'pw', 'ia', 'ia');
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+insert into t1(account_id, login, home_state, work_state)
+select 1, 'pw', 'ak', 'ak' from t1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select count(*) from t1 where account_id = 1;
+count(*)
+3072
+select * from t1
+where (home_state = 'ia' or work_state='ia') and account_id = 1;
+id	account_id	first_name	middle_name	last_name	home_address_1	home_city	home_state	home_postal_code	home_county	home_country	work_address_1	work_city	work_state	work_postal_code	work_county	work_country	login
+1	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+2	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+3	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+4	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+5	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+6	1	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	NULL	NULL	ia	NULL	NULL	NULL	pw
+explain
+select * from t1
+where (home_state = 'ia' or work_state='ia') and account_id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	account_id,user_home_state_indx,user_work_state_indx	user_home_state_indx,user_work_state_indx	3,3	NULL	12	Using union(user_home_state_indx,user_work_state_indx); Using where
+drop table t1;
+CREATE TABLE t1 (
+c1 int(11) NOT NULL auto_increment,
+c2 decimal(10,0) default NULL,
+c3 decimal(10,0) default NULL,
+c4 decimal(10,0) default NULL,
+c5 decimal(10,0) default NULL,
+cp decimal(1,0) default NULL,
+ce decimal(10,0) default NULL,
+cdata char(20),
+PRIMARY KEY  (c1),
+KEY k1 (c2,c3,cp,ce),
+KEY k2 (c4,c5,cp,ce)
+);
+insert into t1 (c2, c3, c4, c5, cp) values(1,1,1,1,1);
+insert into t1 (c2, c3, c4, c5, cp) values(2,1,1,1,4);
+insert into t1 (c2, c3, c4, c5, cp) values(2,1,2,1,1);
+insert into t1 (c2, c3, c4, c5, cp) values(2,1,3,1,4);
+insert into t1 (c2, c3, c4, c5, cp) values(3,1,4,1,4);
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+insert into t1 (c2, c3, c4, c5, cp)
+select c2, c3, c4, c5, cp from t1 where cp = 4;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain
+select * from t1 where (c2=1 and c3=1) or (c4=2 and c5=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	k1,k2	k1,k2	12,12	NULL	2	Using sort_union(k1,k2); Using where
+explain
+select * from t1
+where (c2=1 and c3=1 and cp=1) or (c4=2 and c5=1 and cp=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	k1,k2	k1,k2	14,14	NULL	2	Using sort_union(k1,k2); Using where
+explain
+select * from t1
+where ((c2=1 and c3=1) or (c4=2 and c5=1)) and cp=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	k1,k2	k1,k2	14,14	NULL	2	Using sort_union(k1,k2); Using where
+select * from t1
+where (c2=1 and c3=1 and cp=1) or (c4=2 and c5=1 and cp=1);
+c1	c2	c3	c4	c5	cp	ce	cdata
+1	1	1	1	1	1	NULL	NULL
+3	2	1	2	1	1	NULL	NULL
+select * from t1
+where ((c2=1 and c3=1) or (c4=2 and c5=1)) and cp=1;
+c1	c2	c3	c4	c5	cp	ce	cdata
+1	1	1	1	1	1	NULL	NULL
+3	2	1	2	1	1	NULL	NULL
+drop table t1;
+create table t1 (
+c1 int auto_increment primary key,
+c2 char(20),
+c3 char (20), 
+c4 int
+);
+alter table t1 add key k1 (c2);
+alter table t1 add key k2 (c3);
+alter table t1 add key k3 (c4);
+insert into t1 values(null, 'a', 'b', 0);
+insert into t1 values(null, 'c', 'b', 0);
+insert into t1 values(null, 'a', 'd', 0);
+insert into t1 values(null, 'ccc', 'qqq', 0);
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3) select c2,c3 from t1 where c2 != 'a';
+insert into t1 (c2,c3,c4) select c2,c3,1 from t1 where c2 != 'a';
+insert into t1 (c2,c3,c4) select c2,c3,2 from t1 where c2 != 'a';
+insert into t1 (c2,c3,c4) select c2,c3,3 from t1 where c2 != 'a';
+insert into t1 (c2,c3,c4) select c2,c3,4 from t1 where c2 != 'a';
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select count(*) from t1 where (c2='e' OR c3='q');
+count(*)
+0
+select count(*) from t1 where c4 != 0;
+count(*)
+3840
+explain
+select distinct c1 from t1 where (c2='e' OR c3='q');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	k1,k2	k1,k2	21,21	NULL	2	Using union(k1,k2); Using where
+explain
+select distinct c1 from t1 where (c4!= 0) AND (c2='e' OR c3='q');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	k1,k2,k3	k1,k2	21,21	NULL	2	Using union(k1,k2); Using where
+drop table t1;
+create table t1 (
+id int unsigned auto_increment primary key,
+c1 char(12),
+c2 char(15),
+c3 char(1)
+);
+insert into t1 (c3) values ('1'), ('2');
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+insert into t1 (c3) select c3 from t1;
+update t1 set c1=lpad(id+1000, 12, ' '), c2=lpad(id+10000, 15, ' ');
+alter table t1 add unique index (c1), add unique index (c2), add index (c3);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain
+select * from t1 where (c1='      100000' or c2='         2000000');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	c1,c2	c1,c2	13,16	NULL	2	Using union(c1,c2); Using where
+explain
+select * from t1 where (c1='      100000' or c2='         2000000') and c3='2';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	c1,c2,c3	c1,c2	13,16	NULL	2	Using union(c1,c2); Using where
+select * from t1 where (c1='      100000' or c2='         2000000');
+id	c1	c2	c3
+select * from t1 where (c1='      100000' or c2='         2000000') and c3='2';
+id	c1	c2	c3
+drop table t1;
+CREATE TABLE t1 (
+a smallint DEFAULT NULL,
+pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
+b varchar(10) DEFAULT NULL,
+c varchar(64) DEFAULT NULL,
+INDEX idx1 (a),
+INDEX idx2 (b),
+INDEX idx3 (c)
+);
+SELECT COUNT(*) FROM t1 IGNORE INDEX (idx2,idx3)
+WHERE c  = 'i'  OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' )  OR
+(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ;
+COUNT(*)
+5
+SELECT COUNT(*) FROM t1 
+WHERE c  = 'i'  OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' )  OR
+(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ;
+COUNT(*)
+5
+EXPLAIN
+SELECT COUNT(*) FROM t1 
+WHERE c  = 'i'  OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' )  OR
+(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,idx1,idx2,idx3	idx3,idx2,idx1,PRIMARY	67,13,3,4	NULL	9	Using sort_union(idx3,idx2,idx1,PRIMARY); Using where
+DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int, f2 int, f3 int, f4 int, f5 int,
+PRIMARY KEY (f4), KEY (f1), KEY (f2), KEY (f3)
+) ;
+INSERT INTO t1 VALUES (0,0,NULL,9,5), (0,0,1,9425,NULL);
+SELECT f5 FROM t1
+WHERE f2 != 1 OR f1 IS NULL OR f4 = 4 OR
+f2 AND (f4 BETWEEN 6 AND 255 OR f3 IS NULL);
+f5
+5
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int, f2 int, f3 int, f4 int,
+PRIMARY KEY (f1), KEY (f3), KEY (f4)
+);
+INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0);
+INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL);
+insert into t1 select seq,seq,seq,seq from seq_100_to_400;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SET SESSION optimizer_switch='index_merge_intersection=off';
+SET SESSION optimizer_switch='index_merge_sort_union=off';
+SET SESSION optimizer_switch='index_merge_union=off';
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY,f3,f4	NULL	NULL	NULL	306	Using where
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+f1	f2	f3	f4
+9	0	2	6
+SET SESSION optimizer_switch='index_merge_union=on';
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,f3,f4	PRIMARY,f3	4,5	NULL	5	Using union(PRIMARY,f3); Using where
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+f1	f2	f3	f4
+9	0	2	6
+INSERT INTO t1 VALUES 
+(93,0,3,6), (9933,0,3,3), (94,0,4,6), (9934,0,4,4),
+(95,0,5,6), (9935,0,5,5), (96,0,6,6), (9936,0,6,6),
+(97,0,7,6), (9937,0,7,7), (98,0,8,6), (9938,0,8,8),
+(99,0,9,6), (9939,0,9,9);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SET SESSION optimizer_switch='index_merge_union=off';
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY,f3,f4	NULL	NULL	NULL	320	Using where
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+f1	f2	f3	f4
+9	0	2	6
+SET SESSION optimizer_switch='index_merge_union=on';
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,f3,f4	PRIMARY,f3	4,5	NULL	5	Using union(PRIMARY,f3); Using where
+SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4)
+WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10
+OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 );
+f1	f2	f3	f4
+9	0	2	6
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1;
+CREATE TABLE t1 (f1 int) ;
+INSERT INTO t1 VALUES (0), (0);
+CREATE TABLE t2 (f1 int, f2 int, f3 int, f4 int, INDEX idx (f3,f2)) ;
+INSERT INTO t2 VALUES (5,6,0,0), (0,4,0,0);
+CREATE TABLE t3 (f1 int, f2 int, INDEX idx1 (f2,f1) , INDEX idx2 (f1)) ;
+INSERT INTO t3 VALUES (6,0),( 4,0);
+SELECT * FROM t1,t2,t3
+WHERE (t2.f3 = 1 OR t3.f1=t2.f1) AND t3.f1 <> t2.f2 AND t3.f2 = t2.f4;
+f1	f1	f2	f3	f4	f1	f2
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (
+a int, b int, c int, d int,
+PRIMARY KEY(b), INDEX idx1(d), INDEX idx2(d,b,c)
+);
+INSERT INTO t1 VALUES 
+(0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3),
+(0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SET SESSION optimizer_switch='index_merge_sort_union=off';
+EXPLAIN
+SELECT * FROM t1 
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8  OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY,idx1,idx2	NULL	NULL	NULL	9	Using where
+SELECT * FROM t1 
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8  OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a	b	c	d
+0	58	7	7
+0	64	186	8
+0	73	0	3
+0	74	5	25
+0	75	5	3
+SET SESSION optimizer_switch='index_merge_sort_union=on';
+EXPLAIN
+SELECT * FROM t1 
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8  OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY,idx1,idx2	NULL	NULL	NULL	9	Using where
+SELECT * FROM t1 
+WHERE t1.b>7 AND t1.d>1 AND t1.d<>8  OR t1.d>=7 AND t1.d<8 OR t1.d>7;
+a	b	c	d
+0	58	7	7
+0	64	186	8
+0	73	0	3
+0	74	5	25
+0	75	5	3
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1;
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int, c int, INDEX idx(c,b));
+INSERT INTO t1 VALUES (19,1,NULL), (20,5,7);
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
+(t1.c=0 OR t1.a=500);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,idx	idx	5	NULL	2	Using where; Using index
+SELECT * FROM t1
+WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
+(t1.c=0 OR t1.a=500);
+a	b	c
+DROP TABLE t1;
+CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b));
+INSERT INTO t1 VALUES (167,9999), (168,10000);
+EXPLAIN
+SELECT * FROM t1
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,idx	idx	5	NULL	2	Using where; Using index
+SELECT * FROM t1
+WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2);
+a	b
+167	9999
+168	10000
+DROP TABLE t1;
+#
+# MDEV-8603: Wrong result OR/AND condition over index fields
+#
+CREATE TABLE t1 (
+id INT NOT NULL,
+state VARCHAR(64),
+capital VARCHAR(64),
+UNIQUE KEY (id),
+KEY state (state,id),
+KEY capital (capital, id)
+);
+INSERT INTO t1 VALUES  
+(1,'Arizona','Phoenix'), 
+(2,'Hawaii','Honolulu'),
+(3,'Georgia','Atlanta'), 
+(4,'Florida','Tallahassee'), 
+(5,'Alaska','Juneau'),
+(6,'Michigan','Lansing'),
+(7,'Pennsylvania','Harrisburg'),
+(8,'Virginia','Richmond')
+;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN
+SELECT * FROM t1 FORCE KEY (state,capital) 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) 
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	state,capital	state	71	NULL	8	Using index condition; Using where
+SELECT * FROM t1 FORCE KEY (state,capital) 
+WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9)
+OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
+id	state	capital
+4	Florida	Tallahassee
+3	Georgia	Atlanta
+2	Hawaii	Honolulu
+6	Michigan	Lansing
+7	Pennsylvania	Harrisburg
+8	Virginia	Richmond
+DROP TABLE t1;
+#
+# mdev-11574: do not build index merge of two indexes when
+#             one index is an infix of the other index
+#
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+DROP INDEX Country ON City;
+CREATE INDEX CountryName ON City(Country,Name);
+CREATE INDEX Name ON City(Name);
+select * from City
+where
+Country='FIN' AND Name IN ('Lahti','Imatra') OR
+Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR
+Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR
+Country='DEU' AND Name IN ('Berlin', 'Bonn') OR
+Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR
+Country='PRT' AND Name IN ('Braga', 'Porto') OR
+Country='FRA' AND Name IN ('Paris', 'Marcel') OR
+Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR
+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
+3068	Berlin	DEU	3386667
+3087	Bonn	DEU	301048
+2918	Braga	PRT	90535
+176	Gent	BEL	224180
+3242	Lahti	FIN	96921
+3580	Moscow	RUS	8389200
+1466	Napoli	ITA	1002619
+2807	Oslo	NOR	508726
+2974	Paris	FRA	2125246
+2915	Porto	PRT	273060
+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
+Country='RUS' AND Name IN ('St Petersburg', 'Moscow') OR
+Country='SWE' AND Name IN ('Stockholm', 'Uppsala') OR
+Country='DEU' AND Name IN ('Berlin', 'Bonn') OR
+Country='BEL' AND Name IN ('Antwerpen', 'Gent') OR
+Country='PRT' AND Name IN ('Braga', 'Porto') OR
+Country='FRA' AND Name IN ('Paris', 'Marcel') OR
+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
+DROP DATABASE world;
+set session optimizer_switch='index_merge_sort_intersection=default';
+set global innodb_stats_persistent= @innodb_stats_persistent_save;
+set global innodb_stats_persistent_sample_pages=
+@innodb_stats_persistent_sample_pages_save;
+SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;