path: root/mysql-test/r/index_intersect_innodb.result

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 > 1500000;
COUNT(*)
129
SELECT COUNT(*) FROM City WHERE Population > 300000;
COUNT(*)
1062
SELECT COUNT(*) FROM City WHERE Population > 7000000;
COUNT(*)
14
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	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	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	index_merge	Population,Name	Name,Population	35,4	NULL	#	Using sort_intersect(Name,Population); Using where
EXPLAIN
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City
WHERE Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City 
WHERE Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 300000;
ID	Name	Country	Population
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
131	Melbourne	AUS	2865329
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
215	Manaus	BRA	1255049
223	Maceió	BRA	786288
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
250	Mauá	BRA	375055
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
256	Moji das Cruzes	BRA	339194
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
462	Manchester	GBR	430000
653	Madrid	ESP	2879052
658	Málaga	ESP	530553
661	Murcia	ESP	353504
766	Manila	PHL	1581082
77	Mar del Plata	ARG	512880
778	Makati	PHL	444867
781	Marikina	PHL	391170
783	Muntinlupa	PHL	379310
786	Malabon	PHL	338855
80	Merlo	ARG	463846
83	Moreno	ARG	356993
87	Morón	ARG	349246
942	Medan	IDN	1843919
947	Malang	IDN	716862
962	Manado	IDN	332288
963	Mataram	IDN	306600
SELECT * FROM City 
WHERE Name LIKE 'M%' AND Population > 300000;
ID	Name	Country	Population
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
131	Melbourne	AUS	2865329
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
215	Manaus	BRA	1255049
223	Maceió	BRA	786288
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
250	Mauá	BRA	375055
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
256	Moji das Cruzes	BRA	339194
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
462	Manchester	GBR	430000
653	Madrid	ESP	2879052
658	Málaga	ESP	530553
661	Murcia	ESP	353504
766	Manila	PHL	1581082
77	Mar del Plata	ARG	512880
778	Makati	PHL	444867
781	Marikina	PHL	391170
783	Muntinlupa	PHL	379310
786	Malabon	PHL	338855
80	Merlo	ARG	463846
83	Moreno	ARG	356993
87	Morón	ARG	349246
942	Medan	IDN	1843919
947	Malang	IDN	716862
962	Manado	IDN	332288
963	Mataram	IDN	306600
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 7000000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
3580	Moscow	RUS	8389200
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
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 Name BETWEEN 'G' AND 'K';
COUNT(*)
512
SELECT COUNT(*) FROM City WHERE Population > 1000000;
COUNT(*)
237
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 'B%';
COUNT(*)
339
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	index_merge	Population,Country,Name	Population,Name,Country	4,35,3	NULL	#	Using sort_intersect(Population,Name,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Country,Name	Population,Country,Name	4,3,35	NULL	#	Using sort_intersect(Population,Country,Name); Using where
EXPLAIN
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name,Country	Name,Population,Country	#	NULL	#	Using sort_intersect(Name,Population,Country); 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 > 1000000 AND Country LIKE 'B%';
ID	Name	Country	Population
217	Guarulhos	BRA	1095874
218	Goiânia	BRA	1056330
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
ID	Name	Country	Population
217	Guarulhos	BRA	1095874
218	Goiânia	BRA	1056330
SELECT * FROM City USE INDEX ()
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
ID	Name	Country	Population
1895	Harbin	CHN	4289800
1904	Jinan	CHN	2278100
1905	Hangzhou	CHN	2190500
1914	Guiyang	CHN	1465200
1916	Hefei	CHN	1369100
1923	Jilin	CHN	1040000
1927	Hohhot	CHN	916700
1928	Handan	CHN	840000
1937	Huainan	CHN	700000
1938	Jixi	CHN	683885
1944	Jinzhou	CHN	570000
1950	Hegang	CHN	520000
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
ID	Name	Country	Population
1895	Harbin	CHN	4289800
1904	Jinan	CHN	2278100
1905	Hangzhou	CHN	2190500
1914	Guiyang	CHN	1465200
1916	Hefei	CHN	1369100
1923	Jilin	CHN	1040000
1927	Hohhot	CHN	916700
1928	Handan	CHN	840000
1937	Huainan	CHN	700000
1938	Jixi	CHN	683885
1944	Jinzhou	CHN	570000
1950	Hegang	CHN	520000
SELECT COUNT(*) FROM City WHERE ID BETWEEN 501 AND 1000;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 500;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 2001 AND 2500;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 3701 AND 4000;
COUNT(*)
300
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 Population > 600000;
COUNT(*)
428
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 LIKE 'H%';
COUNT(*)
22
SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z';
COUNT(*)
682
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Country,Population	4,7,4	NULL	#	Using sort_intersect(PRIMARY,Country,Population); Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Country	4,7	NULL	#	Using sort_intersect(PRIMARY,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000
AND Country BETWEEN 'S' AND 'Z';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Country,Population	4,7,4	NULL	#	Using sort_intersect(PRIMARY,Country,Population); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Country,Population	4,7,4	NULL	#	Using sort_intersect(PRIMARY,Country,Population); Using where
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
554	Santiago de Chile	CHL	4703954
SELECT * FROM City
WHERE ID BETWEEN 501 AND 1000 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 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
ID	Name	Country	Population
1	Kabul	AFG	1780000
126	Yerevan	ARM	1248700
130	Sydney	AUS	3276207
131	Melbourne	AUS	2865329
132	Brisbane	AUS	1291117
133	Perth	AUS	1096829
144	Baku	AZE	1787800
56	Luanda	AGO	2022000
69	Buenos Aires	ARG	2982146
70	La Matanza	ARG	1266461
71	Córdoba	ARG	1157507
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
ID	Name	Country	Population
1	Kabul	AFG	1780000
126	Yerevan	ARM	1248700
130	Sydney	AUS	3276207
131	Melbourne	AUS	2865329
132	Brisbane	AUS	1291117
133	Perth	AUS	1096829
144	Baku	AZE	1787800
56	Luanda	AGO	2022000
69	Buenos Aires	ARG	2982146
70	La Matanza	ARG	1266461
71	Córdoba	ARG	1157507
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
ID	Name	Country	Population
2409	Zagreb	HRV	706770
SELECT * FROM City 
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
ID	Name	Country	Population
2409	Zagreb	HRV	706770
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
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 3701 AND 4000 AND Population > 700000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
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 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
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
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
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
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
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	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Country,Name	Population,Country,Name	4,3,35	NULL	#	Using sort_intersect(Population,Country,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	index_merge	Population,Country,Name	Name,Population,Country	35,4,3	NULL	#	Using sort_intersect(Name,Population,Country); Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Country,Population	4,7,4	NULL	#	Using sort_intersect(PRIMARY,Country,Population); Using where
SELECT * FROM City WHERE
Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'B%';
ID	Name	Country	Population
217	Guarulhos	BRA	1095874
218	Goiânia	BRA	1056330
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 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
ID	Name	Country	Population
1	Kabul	AFG	1780000
56	Luanda	AGO	2022000
69	Buenos Aires	ARG	2982146
70	La Matanza	ARG	1266461
71	Córdoba	ARG	1157507
126	Yerevan	ARM	1248700
130	Sydney	AUS	3276207
131	Melbourne	AUS	2865329
132	Brisbane	AUS	1291117
133	Perth	AUS	1096829
144	Baku	AZE	1787800
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
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
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
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 > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	#	Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City 
WHERE Country='CHN' AND Population > 1500000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	#	Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City 
WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	CountryName,Population	38,4	NULL	#	Using sort_intersect(CountryName,Population); Using where
SELECT * FROM City USE INDEX ()
WHERE Country LIKE 'M%' AND Population > 1000000;
ID	Name	Country	Population
2464	Kuala Lumpur	MYS	1297526
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
2698	Maputo	MOZ	1018938
2710	Rangoon (Yangon)	MMR	3361700
SELECT * FROM City 
WHERE Country LIKE 'M%' AND Population > 1000000;
ID	Name	Country	Population
2464	Kuala Lumpur	MYS	1297526
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
2698	Maputo	MOZ	1018938
2710	Rangoon (Yangon)	MMR	3361700
SELECT * FROM City USE INDEX ()
WHERE Country='CHN' AND Population > 1500000;
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
SELECT * FROM City 
WHERE Country='CHN' AND Population > 1500000;
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
SELECT * FROM City USE INDEX ()
WHERE Country='CHN' AND Population > 1500000 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 > 1500000 AND Name LIKE 'C%';
ID	Name	Country	Population
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
EXPLAIN 
SELECT * FROM City, Country 
WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND
Country.Code=City.Country;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	
DROP DATABASE world;
use test;
CREATE TABLE t1 (
f1 int,
f4 varchar(32),
f5 int,
PRIMARY KEY (f1),
KEY (f4)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
(5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6),
(530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1),
(535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2),
(540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0),
(956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0),
(961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL),
(966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0),
(971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0),
(976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7),
(981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1),
(986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7),
(991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4),
(996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2);
EXPLAIN
SELECT * FROM t1
WHERE (f1 < 535  OR  f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	PRIMARY,f4	PRIMARY,f4	4,39	NULL	#	Using sort_intersect(PRIMARY,f4); Using where
SELECT * FROM t1
WHERE (f1 < 535  OR  f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
f1	f4	f5
994	r	2
996	A	2
998	a	0
DROP TABLE t1;
SET SESSION optimizer_switch='index_merge_sort_intersection=on';
SET SESSION STORAGE_ENGINE=DEFAULT;