MWL #21: "index_merge: non-ROR intersection".

The second (final) patch.

9 files changed, 1973 insertions, 270 deletions

diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result
new file mode 100644
index 00000000000..1a89fe897e7
--- /dev/null
+++ b/mysql-test/r/index_intersect.result
@@ -0,0 +1,921 @@
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+DROP DATABASE IF EXISTS world;
+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=on';
+SELECT COUNT(*) FROM City;
+COUNT(*)
+4079
+SELECT COUNT(*) FROM City WHERE Name LIKE 'C%';
+COUNT(*)
+281
+SELECT COUNT(*) FROM City WHERE Name LIKE 'M%';
+COUNT(*)
+301
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 500000;
+COUNT(*)
+539
+SELECT COUNT(*) FROM City WHERE Population > 300000;
+COUNT(*)
+1062
+SELECT COUNT(*) FROM City WHERE Population > 5000000;
+COUNT(*)
+24
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	9	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	21	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 300000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name	35	NULL	164	Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Name LIKE 'M%' AND Population > 5000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population	4	NULL	25	Using where
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City
+WHERE Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+766	Manila	PHL	1581082
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1945	Mudanjiang	CHN	570000
+2259	Medellín	COL	1861265
+2300	Mbuji-Mayi	COD	806475
+2440	Monrovia	LBR	850000
+2487	Marrakech	MAR	621914
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2698	Maputo	MOZ	1018938
+2711	Mandalay	MMR	885300
+2734	Managua	NIC	959000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3434	Mykolajiv	UKR	508000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3540	Maracaíbo	VEN	1304776
+3580	Moscow	RUS	8389200
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+766	Manila	PHL	1581082
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1945	Mudanjiang	CHN	570000
+2259	Medellín	COL	1861265
+2300	Mbuji-Mayi	COD	806475
+2440	Monrovia	LBR	850000
+2487	Marrakech	MAR	621914
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2698	Maputo	MOZ	1018938
+2711	Mandalay	MMR	885300
+2734	Managua	NIC	959000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3434	Mykolajiv	UKR	508000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3540	Maracaíbo	VEN	1304776
+3580	Moscow	RUS	8389200
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 300000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+80	Merlo	ARG	463846
+83	Moreno	ARG	356993
+87	Morón	ARG	349246
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+250	Mauá	BRA	375055
+256	Moji das Cruzes	BRA	339194
+462	Manchester	GBR	430000
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+661	Murcia	ESP	353504
+766	Manila	PHL	1581082
+778	Makati	PHL	444867
+781	Marikina	PHL	391170
+783	Muntinlupa	PHL	379310
+786	Malabon	PHL	338855
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+962	Manado	IDN	332288
+963	Mataram	IDN	306600
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1074	Mysore	IND	480692
+1081	Moradabad	IND	429214
+1098	Malegaon	IND	342595
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1559	Matsuyama	JPN	466133
+1560	Matsudo	JPN	461126
+1578	Machida	JPN	364197
+1595	Miyazaki	JPN	303784
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1882	Mombasa	KEN	461753
+1945	Mudanjiang	CHN	570000
+2005	Ma´anshan	CHN	305421
+2259	Medellín	COL	1861265
+2267	Manizales	COL	337580
+2300	Mbuji-Mayi	COD	806475
+2348	Masan	KOR	441242
+2440	Monrovia	LBR	850000
+2454	Macao	MAC	437500
+2487	Marrakech	MAR	621914
+2491	Meknès	MAR	460000
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2554	Matamoros	MEX	416428
+2557	Mazatlán	MEX	380265
+2698	Maputo	MOZ	1018938
+2699	Matola	MOZ	424662
+2711	Mandalay	MMR	885300
+2712	Moulmein (Mawlamyine)	MMR	307900
+2734	Managua	NIC	959000
+2756	Mushin	NGA	333200
+2757	Maiduguri	NGA	320000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3086	Mannheim	DEU	307730
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3371	Malatya	TUR	330312
+3434	Mykolajiv	UKR	508000
+3435	Mariupol	UKR	490000
+3438	Makijivka	UKR	384000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3522	Mogiljov	BLR	356000
+3540	Maracaíbo	VEN	1304776
+3545	Maracay	VEN	444443
+3547	Maturín	VEN	319726
+3580	Moscow	RUS	8389200
+3622	Magnitogorsk	RUS	427900
+3625	Murmansk	RUS	376300
+3636	Mahat?kala	RUS	332800
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+3834	Mesa	USA	396375
+3837	Minneapolis	USA	382618
+3839	Miami	USA	362470
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 300000;
+ID	Name	Country	Population
+2454	Macao	MAC	437500
+223	Maceió	BRA	786288
+1578	Machida	JPN	364197
+653	Madrid	ESP	2879052
+1042	Madurai	IND	977856
+3622	Magnitogorsk	RUS	427900
+3636	Mahat?kala	RUS	332800
+2757	Maiduguri	NGA	320000
+778	Makati	PHL	444867
+3438	Makijivka	UKR	384000
+786	Malabon	PHL	338855
+658	Málaga	ESP	530553
+947	Malang	IDN	716862
+3371	Malatya	TUR	330312
+1098	Malegaon	IND	342595
+962	Manado	IDN	332288
+2734	Managua	NIC	959000
+215	Manaus	BRA	1255049
+462	Manchester	GBR	430000
+2711	Mandalay	MMR	885300
+766	Manila	PHL	1581082
+2267	Manizales	COL	337580
+3086	Mannheim	DEU	307730
+2698	Maputo	MOZ	1018938
+77	Mar del Plata	ARG	512880
+3540	Maracaíbo	VEN	1304776
+3545	Maracay	VEN	444443
+781	Marikina	PHL	391170
+3435	Mariupol	UKR	490000
+2487	Marrakech	MAR	621914
+2975	Marseille	FRA	798430
+2348	Masan	KOR	441242
+1381	Mashhad	IRN	1887405
+2554	Matamoros	MEX	416428
+963	Mataram	IDN	306600
+2699	Matola	MOZ	424662
+1560	Matsudo	JPN	461126
+1559	Matsuyama	JPN	466133
+3547	Maturín	VEN	319726
+250	Mauá	BRA	375055
+2557	Mazatlán	MEX	380265
+2005	Ma´anshan	CHN	305421
+2300	Mbuji-Mayi	COD	806475
+942	Medan	IDN	1843919
+2259	Medellín	COL	1861265
+3176	Medina	SAU	608300
+1051	Meerut	IND	753778
+3175	Mekka	SAU	965700
+2491	Meknès	MAR	460000
+131	Melbourne	AUS	2865329
+3810	Memphis	USA	650100
+2530	Mérida	MEX	703324
+80	Merlo	ARG	463846
+3364	Mersin (Içel)	TUR	587212
+3834	Mesa	USA	396375
+2526	Mexicali	MEX	764902
+3839	Miami	USA	362470
+1465	Milano	ITA	1300977
+3811	Milwaukee	USA	596974
+3837	Minneapolis	USA	382618
+3520	Minsk	BLR	1674000
+1816	Mississauga	CAN	608072
+1595	Miyazaki	JPN	303784
+3214	Mogadishu	SOM	997000
+3522	Mogiljov	BLR	356000
+256	Moji das Cruzes	BRA	339194
+1882	Mombasa	KEN	461753
+2440	Monrovia	LBR	850000
+2523	Monterrey	MEX	1108499
+3492	Montevideo	URY	1236000
+1810	Montréal	CAN	1016376
+1081	Moradabad	IND	429214
+2537	Morelia	MEX	619958
+83	Moreno	ARG	356993
+87	Morón	ARG	349246
+3580	Moscow	RUS	8389200
+1366	Mosul	IRQ	879000
+2712	Moulmein (Mawlamyine)	MMR	307900
+1945	Mudanjiang	CHN	570000
+2826	Multan	PAK	1182441
+1024	Mumbai (Bombay)	IND	10500000
+3070	Munich [München]	DEU	1194560
+783	Muntinlupa	PHL	379310
+661	Murcia	ESP	353504
+3625	Murmansk	RUS	376300
+2756	Mushin	NGA	333200
+3434	Mykolajiv	UKR	508000
+1074	Mysore	IND	480692
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 5000000;
+ID	Name	Country	Population
+1024	Mumbai (Bombay)	IND	10500000
+3580	Moscow	RUS	8389200
+SELECT * FROM City
+WHERE Name LIKE 'M%' AND Population > 5000000;
+ID	Name	Country	Population
+3580	Moscow	RUS	8389200
+1024	Mumbai (Bombay)	IND	10500000
+SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N';
+COUNT(*)
+301
+SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J';
+COUNT(*)
+408
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 700000;
+COUNT(*)
+358
+SELECT COUNT(*) FROM City WHERE Population > 500000;
+COUNT(*)
+539
+SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
+COUNT(*)
+551
+SELECT COUNT(*) FROM City WHERE Country LIKE 'L%';
+COUNT(*)
+29
+EXPLAIN
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Population,Name	4,35	NULL	9	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'L%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Country	3	NULL	28	Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Country,Name	3,35	NULL	29	Using sort_intersect(Country,Name); Using where
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1810	Montréal	CAN	1016376
+2259	Medellín	COL	1861265
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1810	Montréal	CAN	1016376
+2259	Medellín	COL	1861265
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1895	Harbin	CHN	4289800
+1905	Hangzhou	CHN	2190500
+1914	Guiyang	CHN	1465200
+1916	Hefei	CHN	1369100
+1927	Hohhot	CHN	916700
+1928	Handan	CHN	840000
+1937	Huainan	CHN	700000
+1950	Hegang	CHN	520000
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1895	Harbin	CHN	4289800
+1905	Hangzhou	CHN	2190500
+1914	Guiyang	CHN	1465200
+1916	Hefei	CHN	1369100
+1927	Hohhot	CHN	916700
+1928	Handan	CHN	840000
+1937	Huainan	CHN	700000
+1950	Hegang	CHN	520000
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 500 AND 999;
+COUNT(*)
+500
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 3500 AND 3999;
+COUNT(*)
+500
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 1000;
+COUNT(*)
+1000
+SELECT COUNT(*) FROM City WHERE Population > 700000;
+COUNT(*)
+358
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 300000;
+COUNT(*)
+1062
+SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
+COUNT(*)
+551
+SELECT COUNT(*) FROM City WHERE Country LIKE 'A%';
+COUNT(*)
+107
+SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z';
+COUNT(*)
+682
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Country,Population	4,3,4	NULL	5	Using sort_intersect(PRIMARY,Country,Population); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	Population,Country	4,3	NULL	6	Using sort_intersect(Population,Country); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	Country,PRIMARY	3,4	NULL	65	Using sort_intersect(Country,PRIMARY); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Population	4,4	NULL	44	Using sort_intersect(PRIMARY,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 1 AND 1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	Population	4	NULL	359	Using where
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+555	Puente Alto	CHL	386236
+556	Viña del Mar	CHL	312493
+584	San José	CRI	339131
+SELECT * FROM City 
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+555	Puente Alto	CHL	386236
+556	Viña del Mar	CHL	312493
+584	San José	CRI	339131
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+ID	Name	Country	Population
+3503	Toskent	UZB	2117500
+3539	Caracas	VEN	1975294
+3540	Maracaíbo	VEN	1304776
+3541	Barquisimeto	VEN	877239
+3542	Valencia	VEN	794246
+3769	Ho Chi Minh City	VNM	3980000
+3770	Hanoi	VNM	1410000
+3771	Haiphong	VNM	783133
+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
+3801	San Antonio	USA	1144646
+3802	Detroit	USA	951270
+3803	San Jose	USA	894943
+3804	Indianapolis	USA	791926
+3805	San Francisco	USA	776733
+3806	Jacksonville	USA	735167
+3807	Columbus	USA	711470
+SELECT * FROM City 
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+ID	Name	Country	Population
+3503	Toskent	UZB	2117500
+3539	Caracas	VEN	1975294
+3540	Maracaíbo	VEN	1304776
+3541	Barquisimeto	VEN	877239
+3542	Valencia	VEN	794246
+3769	Ho Chi Minh City	VNM	3980000
+3770	Hanoi	VNM	1410000
+3771	Haiphong	VNM	783133
+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
+3801	San Antonio	USA	1144646
+3802	Detroit	USA	951270
+3803	San Jose	USA	894943
+3804	Indianapolis	USA	791926
+3805	San Francisco	USA	776733
+3806	Jacksonville	USA	735167
+3807	Columbus	USA	711470
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 1 AND  1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+ID	Name	Country	Population
+SELECT * FROM City 
+WHERE ID BETWEEN 1 AND  1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+ID	Name	Country	Population
+SET SESSION sort_buffer_size = 2048;
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name,Population	35,4	NULL	9	Using sort_intersect(Name,Population); Using where
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name	35	NULL	164	Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Name LIKE 'C%' AND Population > 1000000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Name,Population	35,4	NULL	9	Using sort_intersect(Name,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Name	35	NULL	225	Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Name	35	NULL	225	Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	Country,Population	3,4	NULL	6	Using sort_intersect(Country,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID < 1000 AND Population > 700000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	Population	4	NULL	359	Using where
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+1042	Madurai	IND	977856
+658	Málaga	ESP	530553
+947	Malang	IDN	716862
+2734	Managua	NIC	959000
+215	Manaus	BRA	1255049
+2711	Mandalay	MMR	885300
+766	Manila	PHL	1581082
+2698	Maputo	MOZ	1018938
+77	Mar del Plata	ARG	512880
+3540	Maracaíbo	VEN	1304776
+2487	Marrakech	MAR	621914
+2975	Marseille	FRA	798430
+1381	Mashhad	IRN	1887405
+2300	Mbuji-Mayi	COD	806475
+942	Medan	IDN	1843919
+2259	Medellín	COL	1861265
+3176	Medina	SAU	608300
+1051	Meerut	IND	753778
+3175	Mekka	SAU	965700
+131	Melbourne	AUS	2865329
+3810	Memphis	USA	650100
+2530	Mérida	MEX	703324
+3364	Mersin (Içel)	TUR	587212
+2526	Mexicali	MEX	764902
+1465	Milano	ITA	1300977
+3811	Milwaukee	USA	596974
+3520	Minsk	BLR	1674000
+1816	Mississauga	CAN	608072
+3214	Mogadishu	SOM	997000
+2440	Monrovia	LBR	850000
+2523	Monterrey	MEX	1108499
+3492	Montevideo	URY	1236000
+1810	Montréal	CAN	1016376
+2537	Morelia	MEX	619958
+3580	Moscow	RUS	8389200
+1366	Mosul	IRQ	879000
+1945	Mudanjiang	CHN	570000
+2826	Multan	PAK	1182441
+1024	Mumbai (Bombay)	IND	10500000
+3070	Munich [München]	DEU	1194560
+3434	Mykolajiv	UKR	508000
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+2259	Medellín	COL	1861265
+1810	Montréal	CAN	1016376
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1914	Guiyang	CHN	1465200
+1928	Handan	CHN	840000
+1905	Hangzhou	CHN	2190500
+1895	Harbin	CHN	4289800
+1916	Hefei	CHN	1369100
+1950	Hegang	CHN	520000
+1927	Hohhot	CHN	916700
+1937	Huainan	CHN	700000
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City 
+WHERE ID < 1000 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SET SESSION sort_buffer_size = default;
+DROP INDEX Country ON City;
+CREATE INDEX CountryID ON City(Country,ID);
+CREATE INDEX CountryName ON City(Country,Name);
+EXPLAIN
+SELECT * FROM City 
+WHERE Country LIKE 'M%' AND Population > 700000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	23	Using sort_intersect(Population,CountryID); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,CountryID,CountryName	CountryID,Population	3,4	NULL	20	Using sort_intersect(CountryID,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name,CountryID,CountryName	CountryName	38	NULL	13	Using where
+SELECT * FROM City USE INDEX ()
+WHERE Country LIKE 'M%' AND Population > 700000;
+ID	Name	Country	Population
+2464	Kuala Lumpur	MYS	1297526
+2482	Bamako	MLI	809552
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+2516	Guadalajara	MEX	1647720
+2517	Ecatepec de Morelos	MEX	1620303
+2518	Puebla	MEX	1346176
+2519	Nezahualcóyotl	MEX	1224924
+2520	Juárez	MEX	1217818
+2521	Tijuana	MEX	1212232
+2522	León	MEX	1133576
+2523	Monterrey	MEX	1108499
+2524	Zapopan	MEX	1002239
+2525	Naucalpan de Juárez	MEX	857511
+2526	Mexicali	MEX	764902
+2527	Culiacán	MEX	744859
+2528	Acapulco de Juárez	MEX	721011
+2529	Tlalnepantla de Baz	MEX	720755
+2530	Mérida	MEX	703324
+2690	Chisinau	MDA	719900
+2696	Ulan Bator	MNG	773700
+2698	Maputo	MOZ	1018938
+2710	Rangoon (Yangon)	MMR	3361700
+2711	Mandalay	MMR	885300
+SELECT * FROM City 
+WHERE Country LIKE 'M%' AND Population > 700000;
+ID	Name	Country	Population
+2464	Kuala Lumpur	MYS	1297526
+2482	Bamako	MLI	809552
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+2516	Guadalajara	MEX	1647720
+2517	Ecatepec de Morelos	MEX	1620303
+2518	Puebla	MEX	1346176
+2519	Nezahualcóyotl	MEX	1224924
+2520	Juárez	MEX	1217818
+2521	Tijuana	MEX	1212232
+2522	León	MEX	1133576
+2523	Monterrey	MEX	1108499
+2524	Zapopan	MEX	1002239
+2525	Naucalpan de Juárez	MEX	857511
+2526	Mexicali	MEX	764902
+2527	Culiacán	MEX	744859
+2528	Acapulco de Juárez	MEX	721011
+2529	Tlalnepantla de Baz	MEX	720755
+2530	Mérida	MEX	703324
+2690	Chisinau	MDA	719900
+2696	Ulan Bator	MNG	773700
+2698	Maputo	MOZ	1018938
+2710	Rangoon (Yangon)	MMR	3361700
+2711	Mandalay	MMR	885300
+SELECT * FROM City USE INDEX ()
+WHERE Country='CHN' AND Population > 1000000;
+ID	Name	Country	Population
+1890	Shanghai	CHN	9696300
+1891	Peking	CHN	7472000
+1892	Chongqing	CHN	6351600
+1893	Tianjin	CHN	5286800
+1894	Wuhan	CHN	4344600
+1895	Harbin	CHN	4289800
+1896	Shenyang	CHN	4265200
+1897	Kanton [Guangzhou]	CHN	4256300
+1898	Chengdu	CHN	3361500
+1899	Nanking [Nanjing]	CHN	2870300
+1900	Changchun	CHN	2812000
+1901	Xi´an	CHN	2761400
+1902	Dalian	CHN	2697000
+1903	Qingdao	CHN	2596000
+1904	Jinan	CHN	2278100
+1905	Hangzhou	CHN	2190500
+1906	Zhengzhou	CHN	2107200
+1907	Shijiazhuang	CHN	2041500
+1908	Taiyuan	CHN	1968400
+1909	Kunming	CHN	1829500
+1910	Changsha	CHN	1809800
+1911	Nanchang	CHN	1691600
+1912	Fuzhou	CHN	1593800
+1913	Lanzhou	CHN	1565800
+1914	Guiyang	CHN	1465200
+1915	Ningbo	CHN	1371200
+1916	Hefei	CHN	1369100
+1917	Urumt?i [Ürümqi]	CHN	1310100
+1918	Anshan	CHN	1200000
+1919	Fushun	CHN	1200000
+1920	Nanning	CHN	1161800
+1921	Zibo	CHN	1140000
+1922	Qiqihar	CHN	1070000
+1923	Jilin	CHN	1040000
+1924	Tangshan	CHN	1040000
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000;
+ID	Name	Country	Population
+1890	Shanghai	CHN	9696300
+1891	Peking	CHN	7472000
+1892	Chongqing	CHN	6351600
+1893	Tianjin	CHN	5286800
+1894	Wuhan	CHN	4344600
+1895	Harbin	CHN	4289800
+1896	Shenyang	CHN	4265200
+1897	Kanton [Guangzhou]	CHN	4256300
+1898	Chengdu	CHN	3361500
+1899	Nanking [Nanjing]	CHN	2870300
+1900	Changchun	CHN	2812000
+1901	Xi´an	CHN	2761400
+1902	Dalian	CHN	2697000
+1903	Qingdao	CHN	2596000
+1904	Jinan	CHN	2278100
+1905	Hangzhou	CHN	2190500
+1906	Zhengzhou	CHN	2107200
+1907	Shijiazhuang	CHN	2041500
+1908	Taiyuan	CHN	1968400
+1909	Kunming	CHN	1829500
+1910	Changsha	CHN	1809800
+1911	Nanchang	CHN	1691600
+1912	Fuzhou	CHN	1593800
+1913	Lanzhou	CHN	1565800
+1914	Guiyang	CHN	1465200
+1915	Ningbo	CHN	1371200
+1916	Hefei	CHN	1369100
+1917	Urumt?i [Ürümqi]	CHN	1310100
+1918	Anshan	CHN	1200000
+1919	Fushun	CHN	1200000
+1920	Nanning	CHN	1161800
+1921	Zibo	CHN	1140000
+1922	Qiqihar	CHN	1070000
+1923	Jilin	CHN	1040000
+1924	Tangshan	CHN	1040000
+SELECT * FROM City USE INDEX ()
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+ID	Name	Country	Population
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+ID	Name	Country	Population
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+1898	Chengdu	CHN	3361500
+1892	Chongqing	CHN	6351600
+DROP DATABASE world;
+use test;
+SET SESSION optimizer_switch='index_merge_sort_intersection=on';
diff --git a/mysql-test/r/index_intersect_innodb.result b/mysql-test/r/index_intersect_innodb.result
new file mode 100644
index 00000000000..89c2e9faee9
--- /dev/null
+++ b/mysql-test/r/index_intersect_innodb.result
@@ -0,0 +1,923 @@
+SET SESSION STORAGE_ENGINE='InnoDB';
+DROP TABLE IF EXISTS t1,t2,t3,t4;
+DROP DATABASE IF EXISTS world;
+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=on';
+SELECT COUNT(*) FROM City;
+COUNT(*)
+4079
+SELECT COUNT(*) FROM City WHERE Name LIKE 'C%';
+COUNT(*)
+281
+SELECT COUNT(*) FROM City WHERE Name LIKE 'M%';
+COUNT(*)
+301
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 500000;
+COUNT(*)
+539
+SELECT COUNT(*) FROM City WHERE Population > 300000;
+COUNT(*)
+1062
+SELECT COUNT(*) FROM City WHERE Population > 5000000;
+COUNT(*)
+24
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name,Population	35,4	NULL	16	Using sort_intersect(Name,Population); Using where
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	40	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 300000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	79	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Name LIKE 'M%' AND Population > 5000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	1	Using sort_intersect(Population,Name); Using where
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City
+WHERE Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+766	Manila	PHL	1581082
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1945	Mudanjiang	CHN	570000
+2259	Medellín	COL	1861265
+2300	Mbuji-Mayi	COD	806475
+2440	Monrovia	LBR	850000
+2487	Marrakech	MAR	621914
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2698	Maputo	MOZ	1018938
+2711	Mandalay	MMR	885300
+2734	Managua	NIC	959000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3434	Mykolajiv	UKR	508000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3540	Maracaíbo	VEN	1304776
+3580	Moscow	RUS	8389200
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+766	Manila	PHL	1581082
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1945	Mudanjiang	CHN	570000
+2259	Medellín	COL	1861265
+2300	Mbuji-Mayi	COD	806475
+2440	Monrovia	LBR	850000
+2487	Marrakech	MAR	621914
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2698	Maputo	MOZ	1018938
+2711	Mandalay	MMR	885300
+2734	Managua	NIC	959000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3434	Mykolajiv	UKR	508000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3540	Maracaíbo	VEN	1304776
+3580	Moscow	RUS	8389200
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 300000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+80	Merlo	ARG	463846
+83	Moreno	ARG	356993
+87	Morón	ARG	349246
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+250	Mauá	BRA	375055
+256	Moji das Cruzes	BRA	339194
+462	Manchester	GBR	430000
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+661	Murcia	ESP	353504
+766	Manila	PHL	1581082
+778	Makati	PHL	444867
+781	Marikina	PHL	391170
+783	Muntinlupa	PHL	379310
+786	Malabon	PHL	338855
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+962	Manado	IDN	332288
+963	Mataram	IDN	306600
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1074	Mysore	IND	480692
+1081	Moradabad	IND	429214
+1098	Malegaon	IND	342595
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1559	Matsuyama	JPN	466133
+1560	Matsudo	JPN	461126
+1578	Machida	JPN	364197
+1595	Miyazaki	JPN	303784
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1882	Mombasa	KEN	461753
+1945	Mudanjiang	CHN	570000
+2005	Ma´anshan	CHN	305421
+2259	Medellín	COL	1861265
+2267	Manizales	COL	337580
+2300	Mbuji-Mayi	COD	806475
+2348	Masan	KOR	441242
+2440	Monrovia	LBR	850000
+2454	Macao	MAC	437500
+2487	Marrakech	MAR	621914
+2491	Meknès	MAR	460000
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2554	Matamoros	MEX	416428
+2557	Mazatlán	MEX	380265
+2698	Maputo	MOZ	1018938
+2699	Matola	MOZ	424662
+2711	Mandalay	MMR	885300
+2712	Moulmein (Mawlamyine)	MMR	307900
+2734	Managua	NIC	959000
+2756	Mushin	NGA	333200
+2757	Maiduguri	NGA	320000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3086	Mannheim	DEU	307730
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3371	Malatya	TUR	330312
+3434	Mykolajiv	UKR	508000
+3435	Mariupol	UKR	490000
+3438	Makijivka	UKR	384000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3522	Mogiljov	BLR	356000
+3540	Maracaíbo	VEN	1304776
+3545	Maracay	VEN	444443
+3547	Maturín	VEN	319726
+3580	Moscow	RUS	8389200
+3622	Magnitogorsk	RUS	427900
+3625	Murmansk	RUS	376300
+3636	Mahat?kala	RUS	332800
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+3834	Mesa	USA	396375
+3837	Minneapolis	USA	382618
+3839	Miami	USA	362470
+SELECT * FROM City 
+WHERE Name LIKE 'M%' AND Population > 300000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+80	Merlo	ARG	463846
+83	Moreno	ARG	356993
+87	Morón	ARG	349246
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+250	Mauá	BRA	375055
+256	Moji das Cruzes	BRA	339194
+462	Manchester	GBR	430000
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+661	Murcia	ESP	353504
+766	Manila	PHL	1581082
+778	Makati	PHL	444867
+781	Marikina	PHL	391170
+783	Muntinlupa	PHL	379310
+786	Malabon	PHL	338855
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+962	Manado	IDN	332288
+963	Mataram	IDN	306600
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1074	Mysore	IND	480692
+1081	Moradabad	IND	429214
+1098	Malegaon	IND	342595
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1559	Matsuyama	JPN	466133
+1560	Matsudo	JPN	461126
+1578	Machida	JPN	364197
+1595	Miyazaki	JPN	303784
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1882	Mombasa	KEN	461753
+1945	Mudanjiang	CHN	570000
+2005	Ma´anshan	CHN	305421
+2259	Medellín	COL	1861265
+2267	Manizales	COL	337580
+2300	Mbuji-Mayi	COD	806475
+2348	Masan	KOR	441242
+2440	Monrovia	LBR	850000
+2454	Macao	MAC	437500
+2487	Marrakech	MAR	621914
+2491	Meknès	MAR	460000
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2554	Matamoros	MEX	416428
+2557	Mazatlán	MEX	380265
+2698	Maputo	MOZ	1018938
+2699	Matola	MOZ	424662
+2711	Mandalay	MMR	885300
+2712	Moulmein (Mawlamyine)	MMR	307900
+2734	Managua	NIC	959000
+2756	Mushin	NGA	333200
+2757	Maiduguri	NGA	320000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3086	Mannheim	DEU	307730
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3371	Malatya	TUR	330312
+3434	Mykolajiv	UKR	508000
+3435	Mariupol	UKR	490000
+3438	Makijivka	UKR	384000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3522	Mogiljov	BLR	356000
+3540	Maracaíbo	VEN	1304776
+3545	Maracay	VEN	444443
+3547	Maturín	VEN	319726
+3580	Moscow	RUS	8389200
+3622	Magnitogorsk	RUS	427900
+3625	Murmansk	RUS	376300
+3636	Mahat?kala	RUS	332800
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+3834	Mesa	USA	396375
+3837	Minneapolis	USA	382618
+3839	Miami	USA	362470
+SELECT * FROM City USE INDEX ()
+WHERE Name LIKE 'M%' AND Population > 5000000;
+ID	Name	Country	Population
+1024	Mumbai (Bombay)	IND	10500000
+3580	Moscow	RUS	8389200
+SELECT * FROM City
+WHERE Name LIKE 'M%' AND Population > 5000000;
+ID	Name	Country	Population
+1024	Mumbai (Bombay)	IND	10500000
+3580	Moscow	RUS	8389200
+SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N';
+COUNT(*)
+301
+SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J';
+COUNT(*)
+408
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 700000;
+COUNT(*)
+358
+SELECT COUNT(*) FROM City WHERE Population > 500000;
+COUNT(*)
+539
+SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
+COUNT(*)
+551
+SELECT COUNT(*) FROM City WHERE Country LIKE 'L%';
+COUNT(*)
+29
+EXPLAIN
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Population,Name,Country	4,35,3	NULL	2	Using sort_intersect(Population,Name,Country); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'L%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Country,Population	3,4	NULL	2	Using sort_intersect(Country,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Population,Country,Name	4,3,35	NULL	7	Using sort_intersect(Population,Country,Name); Using where
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1810	Montréal	CAN	1016376
+2259	Medellín	COL	1861265
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1810	Montréal	CAN	1016376
+2259	Medellín	COL	1861265
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City USE INDEX ()
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1895	Harbin	CHN	4289800
+1905	Hangzhou	CHN	2190500
+1914	Guiyang	CHN	1465200
+1916	Hefei	CHN	1369100
+1927	Hohhot	CHN	916700
+1928	Handan	CHN	840000
+1937	Huainan	CHN	700000
+1950	Hegang	CHN	520000
+SELECT * FROM City 
+WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1895	Harbin	CHN	4289800
+1905	Hangzhou	CHN	2190500
+1914	Guiyang	CHN	1465200
+1916	Hefei	CHN	1369100
+1927	Hohhot	CHN	916700
+1928	Handan	CHN	840000
+1937	Huainan	CHN	700000
+1950	Hegang	CHN	520000
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 500 AND 999;
+COUNT(*)
+500
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 3500 AND 3999;
+COUNT(*)
+500
+SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 1000;
+COUNT(*)
+1000
+SELECT COUNT(*) FROM City WHERE Population > 700000;
+COUNT(*)
+358
+SELECT COUNT(*) FROM City WHERE Population > 1000000;
+COUNT(*)
+237
+SELECT COUNT(*) FROM City WHERE Population > 300000;
+COUNT(*)
+1062
+SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
+COUNT(*)
+551
+SELECT COUNT(*) FROM City WHERE Country LIKE 'A%';
+COUNT(*)
+107
+SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z';
+COUNT(*)
+682
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Country,Population	4,3,4	NULL	11	Using sort_intersect(PRIMARY,Country,Population); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,3	NULL	1	Using sort_intersect(PRIMARY,Population,Country); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,3	NULL	34	Using sort_intersect(PRIMARY,Population,Country); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Country,Population	4,3,4	NULL	12	Using sort_intersect(PRIMARY,Country,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID BETWEEN 1 AND 1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Country,Population	4,3,4	NULL	21	Using sort_intersect(PRIMARY,Country,Population); Using where
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+555	Puente Alto	CHL	386236
+556	Viña del Mar	CHL	312493
+584	San José	CRI	339131
+SELECT * FROM City 
+WHERE ID BETWEEN 500 AND 999 AND Population > 300000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+555	Puente Alto	CHL	386236
+556	Viña del Mar	CHL	312493
+584	San José	CRI	339131
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+ID	Name	Country	Population
+3503	Toskent	UZB	2117500
+3539	Caracas	VEN	1975294
+3540	Maracaíbo	VEN	1304776
+3541	Barquisimeto	VEN	877239
+3542	Valencia	VEN	794246
+3769	Ho Chi Minh City	VNM	3980000
+3770	Hanoi	VNM	1410000
+3771	Haiphong	VNM	783133
+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
+3801	San Antonio	USA	1144646
+3802	Detroit	USA	951270
+3803	San Jose	USA	894943
+3804	Indianapolis	USA	791926
+3805	San Francisco	USA	776733
+3806	Jacksonville	USA	735167
+3807	Columbus	USA	711470
+SELECT * FROM City 
+WHERE ID BETWEEN 3500 AND 3999 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z';
+ID	Name	Country	Population
+3503	Toskent	UZB	2117500
+3539	Caracas	VEN	1975294
+3540	Maracaíbo	VEN	1304776
+3541	Barquisimeto	VEN	877239
+3542	Valencia	VEN	794246
+3769	Ho Chi Minh City	VNM	3980000
+3770	Hanoi	VNM	1410000
+3771	Haiphong	VNM	783133
+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
+3801	San Antonio	USA	1144646
+3802	Detroit	USA	951270
+3803	San Jose	USA	894943
+3804	Indianapolis	USA	791926
+3805	San Francisco	USA	776733
+3806	Jacksonville	USA	735167
+3807	Columbus	USA	711470
+SELECT * FROM City USE INDEX ()
+WHERE ID BETWEEN 1 AND  1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+ID	Name	Country	Population
+SELECT * FROM City 
+WHERE ID BETWEEN 1 AND  1000 AND Population > 700000
+AND Country BETWEEN 'S' AND 'Z' ;
+ID	Name	Country	Population
+SET SESSION sort_buffer_size = 2048;
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Population,Name	4,35	NULL	16	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name	Name,Population	35,4	NULL	40	Using sort_intersect(Name,Population); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE Name LIKE 'C%' AND Population > 1000000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Population,Name	4,35	NULL	16	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Population,Name	4,35	NULL	36	Using sort_intersect(Population,Name); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country,Name	Name,Population,Country	35,4,3	NULL	7	Using sort_intersect(Name,Population,Country); Using where
+EXPLAIN
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,3	NULL	1	Using sort_intersect(PRIMARY,Population,Country); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE ID < 1000 AND Population > 700000 AND Country LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY,Country,Population	4,3,4	NULL	17	Using sort_intersect(PRIMARY,Country,Population); Using where
+SELECT * FROM City WHERE
+Name LIKE 'C%' AND Population > 1000000;
+ID	Name	Country	Population
+71	Córdoba	ARG	1157507
+151	Chittagong	BGD	1392860
+212	Curitiba	BRA	1584232
+608	Cairo	EGY	6789479
+712	Cape Town	ZAF	2352121
+926	Conakry	GIN	1090610
+1026	Calcutta [Kolkata]	IND	4399819
+1027	Chennai (Madras)	IND	3841396
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+2258	Cali	COL	2077386
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+3539	Caracas	VEN	1975294
+3795	Chicago	USA	2896016
+SELECT * FROM City WHERE
+Name LIKE 'M%' AND Population > 500000;
+ID	Name	Country	Population
+77	Mar del Plata	ARG	512880
+131	Melbourne	AUS	2865329
+215	Manaus	BRA	1255049
+223	Maceió	BRA	786288
+653	Madrid	ESP	2879052
+658	Málaga	ESP	530553
+766	Manila	PHL	1581082
+942	Medan	IDN	1843919
+947	Malang	IDN	716862
+1024	Mumbai (Bombay)	IND	10500000
+1042	Madurai	IND	977856
+1051	Meerut	IND	753778
+1366	Mosul	IRQ	879000
+1381	Mashhad	IRN	1887405
+1465	Milano	ITA	1300977
+1810	Montréal	CAN	1016376
+1816	Mississauga	CAN	608072
+1945	Mudanjiang	CHN	570000
+2259	Medellín	COL	1861265
+2300	Mbuji-Mayi	COD	806475
+2440	Monrovia	LBR	850000
+2487	Marrakech	MAR	621914
+2523	Monterrey	MEX	1108499
+2526	Mexicali	MEX	764902
+2530	Mérida	MEX	703324
+2537	Morelia	MEX	619958
+2698	Maputo	MOZ	1018938
+2711	Mandalay	MMR	885300
+2734	Managua	NIC	959000
+2826	Multan	PAK	1182441
+2975	Marseille	FRA	798430
+3070	Munich [München]	DEU	1194560
+3175	Mekka	SAU	965700
+3176	Medina	SAU	608300
+3214	Mogadishu	SOM	997000
+3364	Mersin (Içel)	TUR	587212
+3434	Mykolajiv	UKR	508000
+3492	Montevideo	URY	1236000
+3520	Minsk	BLR	1674000
+3540	Maracaíbo	VEN	1304776
+3580	Moscow	RUS	8389200
+3810	Memphis	USA	650100
+3811	Milwaukee	USA	596974
+SELECT * FROM City
+WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1810	Montréal	CAN	1016376
+2259	Medellín	COL	1861265
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'M%';
+ID	Name	Country	Population
+2516	Guadalajara	MEX	1647720
+SELECT * FROM City 
+WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+1895	Harbin	CHN	4289800
+1905	Hangzhou	CHN	2190500
+1914	Guiyang	CHN	1465200
+1916	Hefei	CHN	1369100
+1927	Hohhot	CHN	916700
+1928	Handan	CHN	840000
+1937	Huainan	CHN	700000
+1950	Hegang	CHN	520000
+SELECT * FROM City
+WHERE ID BETWEEN 500 AND 999 AND Population > 1000000 AND Country LIKE 'A%';
+ID	Name	Country	Population
+SELECT * FROM City 
+WHERE ID < 1000 AND Population > 700000 AND Country LIKE 'C%';
+ID	Name	Country	Population
+554	Santiago de Chile	CHL	4703954
+SET SESSION sort_buffer_size = default;
+DROP INDEX Country ON City;
+CREATE INDEX CountryID ON City(Country,ID);
+CREATE INDEX CountryName ON City(Country,Name);
+EXPLAIN
+SELECT * FROM City 
+WHERE Country LIKE 'M%' AND Population > 700000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	24	Using sort_intersect(Population,CountryID); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,CountryID,CountryName	CountryID,Population	3,4	NULL	21	Using sort_intersect(CountryID,Population); Using where
+EXPLAIN
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Name,CountryID,CountryName	CountryName,Population	38,4	NULL	1	Using sort_intersect(CountryName,Population); Using where
+SELECT * FROM City USE INDEX ()
+WHERE Country LIKE 'M%' AND Population > 700000;
+ID	Name	Country	Population
+2464	Kuala Lumpur	MYS	1297526
+2482	Bamako	MLI	809552
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+2516	Guadalajara	MEX	1647720
+2517	Ecatepec de Morelos	MEX	1620303
+2518	Puebla	MEX	1346176
+2519	Nezahualcóyotl	MEX	1224924
+2520	Juárez	MEX	1217818
+2521	Tijuana	MEX	1212232
+2522	León	MEX	1133576
+2523	Monterrey	MEX	1108499
+2524	Zapopan	MEX	1002239
+2525	Naucalpan de Juárez	MEX	857511
+2526	Mexicali	MEX	764902
+2527	Culiacán	MEX	744859
+2528	Acapulco de Juárez	MEX	721011
+2529	Tlalnepantla de Baz	MEX	720755
+2530	Mérida	MEX	703324
+2690	Chisinau	MDA	719900
+2696	Ulan Bator	MNG	773700
+2698	Maputo	MOZ	1018938
+2710	Rangoon (Yangon)	MMR	3361700
+2711	Mandalay	MMR	885300
+SELECT * FROM City 
+WHERE Country LIKE 'M%' AND Population > 700000;
+ID	Name	Country	Population
+2464	Kuala Lumpur	MYS	1297526
+2482	Bamako	MLI	809552
+2485	Casablanca	MAR	2940623
+2515	Ciudad de México	MEX	8591309
+2516	Guadalajara	MEX	1647720
+2517	Ecatepec de Morelos	MEX	1620303
+2518	Puebla	MEX	1346176
+2519	Nezahualcóyotl	MEX	1224924
+2520	Juárez	MEX	1217818
+2521	Tijuana	MEX	1212232
+2522	León	MEX	1133576
+2523	Monterrey	MEX	1108499
+2524	Zapopan	MEX	1002239
+2525	Naucalpan de Juárez	MEX	857511
+2526	Mexicali	MEX	764902
+2527	Culiacán	MEX	744859
+2528	Acapulco de Juárez	MEX	721011
+2529	Tlalnepantla de Baz	MEX	720755
+2530	Mérida	MEX	703324
+2690	Chisinau	MDA	719900
+2696	Ulan Bator	MNG	773700
+2698	Maputo	MOZ	1018938
+2710	Rangoon (Yangon)	MMR	3361700
+2711	Mandalay	MMR	885300
+SELECT * FROM City USE INDEX ()
+WHERE Country='CHN' AND Population > 1000000;
+ID	Name	Country	Population
+1890	Shanghai	CHN	9696300
+1891	Peking	CHN	7472000
+1892	Chongqing	CHN	6351600
+1893	Tianjin	CHN	5286800
+1894	Wuhan	CHN	4344600
+1895	Harbin	CHN	4289800
+1896	Shenyang	CHN	4265200
+1897	Kanton [Guangzhou]	CHN	4256300
+1898	Chengdu	CHN	3361500
+1899	Nanking [Nanjing]	CHN	2870300
+1900	Changchun	CHN	2812000
+1901	Xi´an	CHN	2761400
+1902	Dalian	CHN	2697000
+1903	Qingdao	CHN	2596000
+1904	Jinan	CHN	2278100
+1905	Hangzhou	CHN	2190500
+1906	Zhengzhou	CHN	2107200
+1907	Shijiazhuang	CHN	2041500
+1908	Taiyuan	CHN	1968400
+1909	Kunming	CHN	1829500
+1910	Changsha	CHN	1809800
+1911	Nanchang	CHN	1691600
+1912	Fuzhou	CHN	1593800
+1913	Lanzhou	CHN	1565800
+1914	Guiyang	CHN	1465200
+1915	Ningbo	CHN	1371200
+1916	Hefei	CHN	1369100
+1917	Urumt?i [Ürümqi]	CHN	1310100
+1918	Anshan	CHN	1200000
+1919	Fushun	CHN	1200000
+1920	Nanning	CHN	1161800
+1921	Zibo	CHN	1140000
+1922	Qiqihar	CHN	1070000
+1923	Jilin	CHN	1040000
+1924	Tangshan	CHN	1040000
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000;
+ID	Name	Country	Population
+1890	Shanghai	CHN	9696300
+1891	Peking	CHN	7472000
+1892	Chongqing	CHN	6351600
+1893	Tianjin	CHN	5286800
+1894	Wuhan	CHN	4344600
+1895	Harbin	CHN	4289800
+1896	Shenyang	CHN	4265200
+1897	Kanton [Guangzhou]	CHN	4256300
+1898	Chengdu	CHN	3361500
+1899	Nanking [Nanjing]	CHN	2870300
+1900	Changchun	CHN	2812000
+1901	Xi´an	CHN	2761400
+1902	Dalian	CHN	2697000
+1903	Qingdao	CHN	2596000
+1904	Jinan	CHN	2278100
+1905	Hangzhou	CHN	2190500
+1906	Zhengzhou	CHN	2107200
+1907	Shijiazhuang	CHN	2041500
+1908	Taiyuan	CHN	1968400
+1909	Kunming	CHN	1829500
+1910	Changsha	CHN	1809800
+1911	Nanchang	CHN	1691600
+1912	Fuzhou	CHN	1593800
+1913	Lanzhou	CHN	1565800
+1914	Guiyang	CHN	1465200
+1915	Ningbo	CHN	1371200
+1916	Hefei	CHN	1369100
+1917	Urumt?i [Ürümqi]	CHN	1310100
+1918	Anshan	CHN	1200000
+1919	Fushun	CHN	1200000
+1920	Nanning	CHN	1161800
+1921	Zibo	CHN	1140000
+1922	Qiqihar	CHN	1070000
+1923	Jilin	CHN	1040000
+1924	Tangshan	CHN	1040000
+SELECT * FROM City USE INDEX ()
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+ID	Name	Country	Population
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+SELECT * FROM City 
+WHERE Country='CHN' AND Population > 1000000 AND Name LIKE 'C%';
+ID	Name	Country	Population
+1892	Chongqing	CHN	6351600
+1898	Chengdu	CHN	3361500
+1900	Changchun	CHN	2812000
+1910	Changsha	CHN	1809800
+DROP DATABASE world;
+use test;
+SET SESSION optimizer_switch='index_merge_sort_intersection=on';
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index bd49af16105..feef9007995 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -1,3 +1,5 @@
+set @optimizer_switch_save= @@optimizer_switch;
+set optimizer_switch='index_merge_sort_intersection=off';
 #---------------- Index merge test 2 -------------------------------------------
 SET SESSION STORAGE_ENGINE = InnoDB;
 drop table if exists t1,t2;
@@ -636,3 +638,4 @@ WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
 COUNT(*)
 6145
 DROP TABLE t1;
+set optimizer_switch= @optimizer_switch_save;
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 0a017ff8c8a..91a1ec6b931 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -1,3 +1,5 @@
+set @optimizer_switch_save= @@optimizer_switch;
+set optimizer_switch='index_merge_sort_intersection=off';
 #---------------- Index merge test 1 -------------------------------------------
 SET SESSION STORAGE_ENGINE = MyISAM;
 drop table if exists t0, t1, t2, t3, t4;
@@ -205,10 +207,10 @@ 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;
 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
+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
 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,
@@ -569,9 +571,7 @@ INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT * FROM t1;
 INSERT INTO t1 SELECT * FROM t1;
-SET SESSION sort_buffer_size=1;
-Warnings:
-Warning	1292	Truncated incorrect sort_buffer_size value: '1'
+SET SESSION sort_buffer_size=1024*8;
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
 ORDER BY a,b;
@@ -1421,19 +1421,19 @@ drop table t1;
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off
 set optimizer_switch='index_merge=off,index_merge_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off
 set optimizer_switch='index_merge_union=on';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off
 set optimizer_switch='default,index_merge_sort_union=off';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=on
 set optimizer_switch=4;
 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4'
 set optimizer_switch=NULL;
@@ -1460,21 +1460,21 @@ set optimizer_switch=default;
 set optimizer_switch='index_merge=off,index_merge_union=off,default';
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on
 set optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on
 set @@global.optimizer_switch=default;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on
 #
 # Check index_merge's @@optimizer_switch flags
 #
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, c int, filler char(100), 
@@ -1551,7 +1551,7 @@ explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	49	Using where
 No intersect if it is disabled:
-set optimizer_switch='default,index_merge_intersection=off';
+set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off';
 explain select * from t1 where a=10 and b=10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	49	Using where
@@ -1584,5 +1584,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 set optimizer_switch=default;
 show variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on
 drop table t0, t1;
+set optimizer_switch= @optimizer_switch_save;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 6827fd0bc76..06cb2cb5620 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1421,9 +1421,9 @@ DROP TABLE t1;
 #
 create table t1(a int, b tinytext);
 insert into t1 values (1,2),(3,2);
-set session sort_buffer_size= 30000;
+set session sort_buffer_size= 1000;
 Warnings:
-Warning	1292	Truncated incorrect sort_buffer_size value: '30000'
+Warning	1292	Truncated incorrect sort_buffer_size value: '1000'
 set session max_sort_length= 2180;
 select * from t1 order by b;
 ERROR HY001: Out of sort memory; increase server sort buffer size
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index 0f9c46f182d..b616afc9578 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -38,6 +38,7 @@ 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);
@@ -71,9 +72,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 +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	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 +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
@@ -328,9 +325,9 @@ 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 < 30) OR (ID BETWEEN 100 AND 150);
 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	81	Using where
 EXPLAIN
 SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 300 AND 600);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -353,11 +350,11 @@ 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 < 30) 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	41	Using where
 EXPLAIN
 SELECT * FROM City
 WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
@@ -373,7 +370,7 @@ OR ((ID BETWEEN 300 AND 600) AND
 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 < 30) 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
@@ -384,13 +381,10 @@ ID	Name	Country	Population
 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 < 30) 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
@@ -401,9 +395,6 @@ ID	Name	Country	Population
 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()
@@ -726,119 +717,55 @@ 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	135	Using where
 EXPLAIN
-SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
+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	81	Using where
+1	SIMPLE	City	range	Population	Population	4	NULL	39	Using where
 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	267	Using where
 EXPLAIN
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+WHERE ((Population > 101000 AND Population < 102000) 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	15	Using sort_union(CountryPopulation,Name); Using where
+1	SIMPLE	City	index_merge	Population,Country,Name,CountryPopulation	CountryPopulation,Name	7,35	NULL	8	Using sort_union(CountryPopulation,Name); Using where
 EXPLAIN
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
-AND Country='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%' )
+AND Country='USA' AND Name LIKE '%port';
 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
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+WHERE ((Population > 101000 AND Population < 102000) 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%')
+WHERE ((Population > 101000 AND Population < 102000) 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='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%')
+AND Country='USA' AND Name LIKE '%port';
 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
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 SELECT * FROM City USE INDEX ()
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
-AND Country='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%')
+AND Country='USA' AND Name LIKE '%port';
 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
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 CREATE INDEX CountryName ON City(Country,Name);
 EXPLAIN
 SELECT * FROM City WHERE Country='USA';
@@ -900,9 +827,10 @@ 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	11	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='USA'
-        AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 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
 SELECT * FROM City USE INDEX ()
@@ -938,21 +866,21 @@ ID	Name	Country	Population
 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 4025 AND 4035);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 ID	Name	Country	Population
-4030	Sandy	USA	101853
-4031	Athens-Clarke County	USA	101489
-4032	Cambridge	USA	101355
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 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);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 ID	Name	Country	Population
-4030	Sandy	USA	101853
-4031	Athens-Clarke County	USA	101489
-4032	Cambridge	USA	101355
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 EXPLAIN
 SELECT * FROM City
 WHERE ((Population > 101000 and Population < 102000) OR
@@ -1386,3 +1314,4 @@ WHERE c  = 'i'  OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' )  OR
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	PRIMARY,idx1,idx2,idx3	idx3,idx2,PRIMARY,idx1	67,13,4,3	NULL	8	Using sort_union(idx3,idx2,PRIMARY,idx1); Using where
 DROP TABLE t1;
+set session optimizer_switch='index_merge_sort_intersection=default';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index 2251119996a..037e23eae5d 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -39,6 +39,7 @@ 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);
@@ -72,9 +73,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 +90,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 +155,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,9 +326,9 @@ 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 < 30) OR (ID BETWEEN 100 AND 150);
 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	79	Using where
 EXPLAIN
 SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 300 AND 600);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -354,11 +351,11 @@ 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 < 30) 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	40	Using where
 EXPLAIN
 SELECT * FROM City
 WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
@@ -372,9 +369,9 @@ WHERE ((ID < 600) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
 OR ((ID BETWEEN 300 AND 600) 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	index_merge	PRIMARY,Population,Country,Name	Name,Country,Population	35,3,4	NULL	188	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 < 30) 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
@@ -385,13 +382,10 @@ ID	Name	Country	Population
 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 < 30) 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
@@ -402,9 +396,6 @@ ID	Name	Country	Population
 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()
@@ -577,6 +568,20 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
 OR ((ID BETWEEN 300 AND 600) AND 
 (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
 ID	Name	Country	Population
+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
 1	Kabul	AFG	1780000
 2	Qandahar	AFG	237500
 3	Herat	AFG	186800
@@ -601,20 +606,6 @@ ID	Name	Country	Population
 68	Ajman	ARE	114395
 129	Oranjestad	ABW	29034
 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
@@ -727,119 +718,55 @@ 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	235	Using where
 EXPLAIN
-SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
+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	80	Using where
+1	SIMPLE	City	range	Population	Population	4	NULL	38	Using where
 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 where
 EXPLAIN
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+WHERE ((Population > 101000 AND Population < 102000) 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	17	Using sort_union(CountryPopulation,Name); Using where
+1	SIMPLE	City	index_merge	Population,Country,Name,CountryPopulation	CountryPopulation,Name	7,35	NULL	10	Using sort_union(CountryPopulation,Name); Using where
 EXPLAIN
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
-AND Country='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%' )
+AND Country='USA' AND Name LIKE '%port';
 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
 SELECT * FROM City 
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
+WHERE ((Population > 101000 AND Population < 102000) 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%')
+WHERE ((Population > 101000 AND Population < 102000) 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='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%')
+AND Country='USA' AND Name LIKE '%port';
 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
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 SELECT * FROM City USE INDEX ()
-WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
-AND Country='USA';
+WHERE ((Population > 101000 AND Population < 1000000) OR Name LIKE 'P%')
+AND Country='USA' AND Name LIKE '%port';
 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
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 CREATE INDEX CountryName ON City(Country,Name);
 EXPLAIN
 SELECT * FROM City WHERE Country='USA';
@@ -901,9 +828,10 @@ 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='USA'
-        AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 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
 SELECT * FROM City USE INDEX ()
@@ -939,21 +867,21 @@ ID	Name	Country	Population
 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 4025 AND 4035);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 ID	Name	Country	Population
-4030	Sandy	USA	101853
-4031	Athens-Clarke County	USA	101489
-4032	Cambridge	USA	101355
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 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);
+WHERE ((Population > 101000 AND Population < 1000000) OR
+ID BETWEEN 3000 AND 3800) AND Country='USA'
+        AND (Name BETWEEN 'P' AND 'Z' OR ID BETWEEN 3500 AND 4300)
+AND Name LIKE '%port';
 ID	Name	Country	Population
-4030	Sandy	USA	101853
-4031	Athens-Clarke County	USA	101489
-4032	Cambridge	USA	101355
+3880	Shreveport	USA	200145
+3946	Bridgeport	USA	139529
 EXPLAIN
 SELECT * FROM City
 WHERE ((Population > 101000 and Population < 102000) OR
@@ -1387,4 +1315,5 @@ WHERE c  = 'i'  OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' )  OR
 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;
+set session optimizer_switch='index_merge_sort_intersection=default';
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cbb6149a148..9cebd8b9f98 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3667,8 +3667,6 @@ CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b));
 CREATE TABLE t2 (x int auto_increment, y int, z int,
 PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b));
 SET SESSION sort_buffer_size = 32 * 1024;
-Warnings:
-Warning	1292	Truncated incorrect sort_buffer_size value: '32768'
 SELECT SQL_NO_CACHE COUNT(*)
 FROM (SELECT  a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c
 FROM t1) t;
@@ -4104,8 +4102,6 @@ INSERT INTO `t1` VALUES ('asdf','2007-02-08 01:11:26');
 INSERT INTO `t2` VALUES ('abcdefghijk');
 INSERT INTO `t2` VALUES ('asdf');
 SET session sort_buffer_size=8192;
-Warnings:
-Warning	1292	Truncated incorrect sort_buffer_size value: '8192'
 SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2;
 d1
 1
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 832c679f8d5..f1133b7fb4d 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -971,6 +971,7 @@ tmpdir	#
 select * from information_schema.session_variables where variable_name like 'tmpdir';
 VARIABLE_NAME	VARIABLE_VALUE
 TMPDIR	#
+set sort_buffer_size=1024*8;
 select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
 @@ssl_ca	@@ssl_capath	@@ssl_cert	@@ssl_cipher	@@ssl_key
 #	#	#	#	#