DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
set @save_optimizer_switch=@@optimizer_switch;
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='outer_join_with_cache=on';
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
);
CREATE TABLE City (
ID int(11) NOT NULL,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0'
);
CREATE TABLE CountryLanguage (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0'
);
SELECT COUNT(*) FROM Country;
COUNT(*)
239
SELECT COUNT(*) FROM City;
COUNT(*)
4079
SELECT COUNT(*) FROM CountryLanguage;
COUNT(*)
984
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=2;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	2
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (incremental, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=default;
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=2;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	2
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; Using join buffer (incremental, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
set join_cache_level=default;
set join_buffer_size=default;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
DROP DATABASE world;
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)
);
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash	Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	hash	PRIMARY	PRIMARY	33	world.Country.Code,const	984	Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash	Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	hash	PRIMARY	PRIMARY	33	world.Country.Code,const	984	Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
set join_cache_level=5;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	5
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where; Using join buffer (flat, BKA join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	6
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where; Using join buffer (flat, BKA join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=7;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	7
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where; Using join buffer (flat, BKAH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=8;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	8
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where; Using join buffer (flat, BKAH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash	Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash	Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	hash	Population,Country	Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=5;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	5
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	6
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKA join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=7;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	7
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=8;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	8
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
Leiden	Netherlands	Dutch
La Matanza	Argentina	Spanish
Lomas de Zamora	Argentina	Spanish
La Plata	Argentina	Spanish
Lanús	Argentina	Spanish
Las Heras	Argentina	Spanish
La Rioja	Argentina	Spanish
Liège	Belgium	Dutch
La Paz	Bolivia	Spanish
Londrina	Brazil	Portuguese
Limeira	Brazil	Portuguese
Lages	Brazil	Portuguese
Luziânia	Brazil	Portuguese
Lauro de Freitas	Brazil	Portuguese
Linhares	Brazil	Portuguese
London	United Kingdom	English
Liverpool	United Kingdom	English
Leeds	United Kingdom	English
Leicester	United Kingdom	English
Luton	United Kingdom	English
Los Angeles	Chile	Spanish
La Serena	Chile	Spanish
La Romana	Dominican Republic	Spanish
Loja	Ecuador	Spanish
Luxor	Egypt	Arabic
Las Palmas de Gran Canaria	Spain	Spanish
L´Hospitalet de Llobregat	Spain	Spanish
Leganés	Spain	Spanish
León	Spain	Spanish
Logroño	Spain	Spanish
Lleida (Lérida)	Spain	Spanish
Le-Cap-Haïtien	Haiti	Haiti Creole
La Ceiba	Honduras	Spanish
Livorno	Italy	Italian
Latina	Italy	Italian
Lecce	Italy	Italian
La Spezia	Italy	Italian
Linz	Austria	German
London	Canada	English
Laval	Canada	English
Longueuil	Canada	English
Lanzhou	China	Chinese
Luoyang	China	Chinese
Liuzhou	China	Chinese
Liaoyang	China	Chinese
Liupanshui	China	Chinese
Liaoyuan	China	Chinese
Lianyungang	China	Chinese
Leshan	China	Chinese
Linyi	China	Chinese
Luzhou	China	Chinese
Laiwu	China	Chinese
Liaocheng	China	Chinese
Laizhou	China	Chinese
Linfen	China	Chinese
Liangcheng	China	Chinese
Longkou	China	Chinese
Langfang	China	Chinese
Liu´an	China	Chinese
Longjing	China	Chinese
Lengshuijiang	China	Chinese
Laiyang	China	Chinese
Longyan	China	Chinese
Linhe	China	Chinese
Leiyang	China	Chinese
Loudi	China	Chinese
Luohe	China	Chinese
Linqing	China	Chinese
Laohekou	China	Chinese
Linchuan	China	Chinese
Lhasa	China	Chinese
Lianyuan	China	Chinese
Liyang	China	Chinese
Liling	China	Chinese
Linhai	China	Chinese
Larisa	Greece	Greek
La Habana	Cuba	Spanish
Lilongwe	Malawi	Chichewa
León	Mexico	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lagos de Moreno	Mexico	Spanish
Lerdo	Mexico	Spanish
Los Cabos	Mexico	Spanish
Lerma	Mexico	Spanish
Las Margaritas	Mexico	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lalitapur	Nepal	Nepali
León	Nicaragua	Spanish
Lambaré	Paraguay	Spanish
Lima	Peru	Spanish
Lisboa	Portugal	Portuguese
Lódz	Poland	Polish
Lublin	Poland	Polish
Legnica	Poland	Polish
Lyon	France	French
Le Havre	France	French
Lille	France	French
Le Mans	France	French
Limoges	France	French
Linköping	Sweden	Swedish
Lund	Sweden	Swedish
Leipzig	Germany	German
Lübeck	Germany	German
Ludwigshafen am Rhein	Germany	German
Leverkusen	Germany	German
Lünen	Germany	German
Lahti	Finland	Finnish
Lausanne	Switzerland	German
Latakia	Syria	Arabic
Luchou	Taiwan	Min
Lungtan	Taiwan	Min
Liberec	Czech Republic	Czech
Lviv	Ukraine	Ukrainian
Lugansk	Ukraine	Ukrainian
Lutsk	Ukraine	Ukrainian
Lysyt?ansk	Ukraine	Ukrainian
Lower Hutt	New Zealand	English
Lida	Belarus	Belorussian
Los Teques	Venezuela	Spanish
Lipetsk	Russian Federation	Russian
Ljubertsy	Russian Federation	Russian
Leninsk-Kuznetski	Russian Federation	Russian
Long Xuyen	Vietnam	Vietnamese
Los Angeles	United States	English
Las Vegas	United States	English
Long Beach	United States	English
Lexington-Fayette	United States	English
Louisville	United States	English
Lincoln	United States	English
Lubbock	United States	English
Little Rock	United States	English
Laredo	United States	English
Lakewood	United States	English
Lansing	United States	English
Lancaster	United States	English
Lafayette	United States	English
Lowell	United States	English
Livonia	United States	English
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BKAH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=default;
set join_buffer_size=default;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	131072
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
set join_cache_level=1;
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
Name	Name
Sydney	Australia
Dhaka	Bangladesh
São Paulo	Brazil
Rio de Janeiro	Brazil
London	United Kingdom
Santiago de Chile	Chile
Cairo	Egypt
Alexandria	Egypt
Jakarta	Indonesia
Mumbai (Bombay)	India
Delhi	India
Calcutta [Kolkata]	India
Chennai (Madras)	India
Baghdad	Iraq
Teheran	Iran
Tokyo	Japan
Jokohama [Yokohama]	Japan
Shanghai	China
Peking	China
Chongqing	China
Tianjin	China
Wuhan	China
Harbin	China
Shenyang	China
Kanton [Guangzhou]	China
Chengdu	China
Santafé de Bogotá	Colombia
Kinshasa	Congo, The Democratic Republic of the
Seoul	South Korea
Pusan	South Korea
Ciudad de México	Mexico
Rangoon (Yangon)	Myanmar
Karachi	Pakistan
Lahore	Pakistan
Lima	Peru
Berlin	Germany
Riyadh	Saudi Arabia
Singapore	Singapore
Bangkok	Thailand
Istanbul	Turkey
Ankara	Turkey
Moscow	Russian Federation
St Petersburg	Russian Federation
Ho Chi Minh City	Vietnam
New York	United States
Los Angeles	United States
set join_cache_level=8;
set join_buffer_size=384;
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	Population,Country	Population	4	NULL	#	Using index condition; Using MRR
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	Using join buffer (flat, BKAH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
Name	Name
Alexandria	Egypt
Ankara	Turkey
Baghdad	Iraq
Bangkok	Thailand
Berlin	Germany
Cairo	Egypt
Calcutta [Kolkata]	India
Chengdu	China
Chennai (Madras)	India
Chongqing	China
Ciudad de México	Mexico
Delhi	India
Dhaka	Bangladesh
Harbin	China
Ho Chi Minh City	Vietnam
Istanbul	Turkey
Jakarta	Indonesia
Jokohama [Yokohama]	Japan
Kanton [Guangzhou]	China
Karachi	Pakistan
Kinshasa	Congo, The Democratic Republic of the
Lahore	Pakistan
Lima	Peru
London	United Kingdom
Los Angeles	United States
Moscow	Russian Federation
Mumbai (Bombay)	India
New York	United States
Peking	China
Pusan	South Korea
Rangoon (Yangon)	Myanmar
Rio de Janeiro	Brazil
Riyadh	Saudi Arabia
Santafé de Bogotá	Colombia
Santiago de Chile	Chile
Seoul	South Korea
Shanghai	China
Shenyang	China
Singapore	Singapore
St Petersburg	Russian Federation
Sydney	Australia
São Paulo	Brazil
Teheran	Iran
Tianjin	China
Tokyo	Japan
Wuhan	China
set join_buffer_size=default;
set join_cache_level=6;
ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default '';
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default '';
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
Vientiane	Laos
Riga	Latvia
Daugavpils	Latvia
Maseru	Lesotho
Beirut	Lebanon
Tripoli	Lebanon
Monrovia	Liberia
Tripoli	Libyan Arab Jamahiriya
Bengasi	Libyan Arab Jamahiriya
Misrata	Libyan Arab Jamahiriya
Vilnius	Lithuania
Kaunas	Lithuania
Klaipeda	Lithuania
?iauliai	Lithuania
Panevezys	Lithuania
ALTER TABLE Country ADD COLUMN PopulationBar text;
UPDATE Country 
SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int));
SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name	PopulationBar
Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
set join_buffer_size=256;
SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name	PopulationBar
Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
set join_cache_level=default;
set join_buffer_size=default;
DROP DATABASE world;
use test;
CREATE TABLE t1(
affiliatetometaid int  NOT NULL default '0',
uniquekey int NOT NULL default '0',
metaid int  NOT NULL default '0',
affiliateid int  NOT NULL default '0',
xml text,
isactive char(1) NOT NULL default 'Y',
PRIMARY KEY  (affiliatetometaid)
);
CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey);
CREATE INDEX t1_affiliateid ON t1(affiliateid);
CREATE INDEX t1_metaid on t1 (metaid);
INSERT INTO t1 VALUES
(1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y');
CREATE TABLE t2(
metaid int  NOT NULL default '0',
name varchar(80) NOT NULL default '',
dateadded timestamp NOT NULL ,
xml text,
status int default NULL,
origin int default NULL,
gid int NOT NULL default '1',
formattypeid int  default NULL,
PRIMARY KEY  (metaid)
);
CREATE INDEX t2_status ON t2(status);
CREATE INDEX t2_gid ON t2(gid);
CREATE INDEX t2_formattypeid ON t2(formattypeid);
INSERT INTO t2 VALUES
(1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL),
(1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL);
CREATE TABLE t3(
mediaid int  NOT NULL ,
metaid int  NOT NULL default '0',
formatid int  NOT NULL default '0',
status int default NULL,
path varchar(100) NOT NULL default '',
datemodified timestamp NOT NULL ,
resourcetype int  NOT NULL default '1',
parameters text,
signature int  default NULL,
quality int  NOT NULL default '255',
PRIMARY KEY  (mediaid)
);
CREATE INDEX t3_metaid ON t3(metaid);
CREATE INDEX t3_formatid ON t3(formatid);
CREATE INDEX t3_status ON t3(status);
CREATE INDEX t3_metaidformatid ON t3(metaid,formatid);
CREATE INDEX t3_signature ON t3(signature);
CREATE INDEX t3_quality ON t3(quality);
INSERT INTO t3 VALUES
(6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255),
(3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255);
CREATE TABLE t4(
formatid int  NOT NULL ,
name varchar(60) NOT NULL default '',
formatclassid int  NOT NULL default '0',
mime varchar(60) default NULL,
extension varchar(10) default NULL,
priority int NOT NULL default '0',
canaddtocapability char(1) NOT NULL default 'Y',
PRIMARY KEY  (formatid)
);
CREATE INDEX t4_formatclassid ON t4(formatclassid);
CREATE INDEX t4_formats_idx ON t4(canaddtocapability);
INSERT INTO t4 VALUES
(19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'),
(54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y');
CREATE TABLE t5(
formatclassid int  NOT NULL ,
name varchar(60) NOT NULL default '',
priority int NOT NULL default '0',
formattypeid int  NOT NULL default '0',
PRIMARY KEY  (formatclassid)
);
CREATE INDEX t5_formattypeid on t5(formattypeid);
INSERT INTO t5 VALUES
(11, "Info", 0, 4), (13, "Digital Audio", 0, 2);
CREATE TABLE t6(
formattypeid int  NOT NULL ,
name varchar(60) NOT NULL default '',
priority int default NULL,
PRIMARY KEY  (formattypeid)
);
INSERT INTO t6 VALUES
(2, "Ringtones", 0);
CREATE TABLE t7(
metaid int  NOT NULL default '0',
artistid int  NOT NULL default '0',
PRIMARY KEY  (metaid,artistid)
);
INSERT INTO t7 VALUES
(4, 5), (3, 4);
CREATE TABLE t8(
artistid int  NOT NULL ,
name varchar(80) NOT NULL default '',
PRIMARY KEY  (artistid)
);
INSERT INTO t8 VALUES
(5, "Anastacia"), (4, "John Mayer");
CREATE TABLE t9(
subgenreid int  NOT NULL default '0',
metaid int  NOT NULL default '0',
PRIMARY KEY  (subgenreid,metaid)
) ;
CREATE INDEX t9_subgenreid ON t9(subgenreid);
CREATE INDEX t9_metaid ON t9(metaid);
INSERT INTO t9 VALUES 
(138, 4), (31, 3);
CREATE TABLE t10(
subgenreid int  NOT NULL ,
genreid int  NOT NULL default '0',
name varchar(80) NOT NULL default '',
PRIMARY KEY  (subgenreid)
) ;
CREATE INDEX t10_genreid ON t10(genreid);
INSERT INTO t10 VALUES 
(138, 19, ''), (31, 3, '');
CREATE TABLE t11(
genreid int  NOT NULL default '0',
name char(80) NOT NULL default '',
priority int NOT NULL default '0',
masterclip char(1) default NULL,
PRIMARY KEY  (genreid)
) ;
CREATE INDEX t11_masterclip ON t11( masterclip);
INSERT INTO t11 VALUES
(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y');
set join_cache_level=6;
EXPLAIN
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid, 
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
t10.subgenreid, t10.name AS subgenreName,
t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
t4.priority + t5.priority + t6.priority AS overallPriority,
t3.path AS path, t3.mediaid, 
t4.formatid, t4.name AS formatName, 
t5.formatclassid, t5.name AS formatclassName, 
t6.formattypeid, t6.name AS formattypeName 
FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
t1.metaid = t2.metaid AND t1.affiliateid = '2';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t6	system	PRIMARY	NULL	NULL	NULL	1	
1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where; Using join buffer (flat, BKA join)
1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where; Using join buffer (incremental, BKA join)
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (incremental, BKA join)
1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t2.metaid	1	Using where; Using index
1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (incremental, BKA join)
1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (incremental, BNL join)
1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (incremental, BKA join)
1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (incremental, BKA join)
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid, 
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
t10.subgenreid, t10.name AS subgenreName,
t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
t4.priority + t5.priority + t6.priority AS overallPriority,
t3.path AS path, t3.mediaid, 
t4.formatid, t4.name AS formatName, 
t5.formatclassid, t5.name AS formatclassName, 
t6.formattypeid, t6.name AS formattypeName 
FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
t1.metaid = t2.metaid AND t1.affiliateid = '2';
uniquekey	affiliateXml	artistName	artistid	genreName	genreid	genrePriority	subgenreid	subgenreName	metaName	metaid	metaXml	overallPriority	path	mediaid	formatid	formatName	formatclassid	formatclassName	formattypeid	formattypeName
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' );
CREATE TABLE t2 (
a2 int, b2 int, filler2 char(64) default ' ', 
PRIMARY KEY idx(a2,b2,filler2)
) ;
CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3));
INSERT INTO t1(a1) VALUES 
(4), (7), (1), (9), (8), (5), (3), (6), (2);
INSERT INTO t2(a2,b2) VALUES
(1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56),
(4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81),
(5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51),
(9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79),
(3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11);
INSERT INTO t3 VALUES
(30,302), (92,923), (18,187), (45,459), (30,309), 
(39,393), (68,685), (45,458), (21,210), (81,817),
(40,405), (61,618), (73,738), (92,929), (27,275),
(18,188), (84,846), (56,564), (14,144), (76,763), 
(98,982), (55,551), (17,174), (99,998), (51,513),
(28,282), (52,527), (33,336), (13,138), (87,878), 
(43,431), (91,916), (62,624), (79,797), (49,494),
(93,933), (34,347), (82,829), (78,780), (63,634), 
(32,329), (22,228), (11,114), (74,749), (23,236);
set join_cache_level=1;
EXPLAIN
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
a1<>a2	a1	a2	b2	b3	c3	s1	s2
0	4	4	13	13	138		
0	4	4	18	18	188		
0	1	1	30	30	309		
0	1	1	32	32	329		
0	9	9	22	22	228		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	5	5	82	82	829		
0	5	5	87	87	878		
0	3	3	45	45	459		
0	3	3	45	45	458		
0	6	6	73	73	738		
0	6	6	74	74	749		
0	2	2	61	61	618		
set join_cache_level=5;
set join_buffer_size=512;
EXPLAIN
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where; Using join buffer (flat, BKA join)
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
a1<>a2	a1	a2	b2	b3	c3	s1	s2
0	4	4	18	18	188		
0	4	4	13	13	138		
0	1	1	30	30	309		
0	1	1	32	32	329		
0	9	9	22	22	228		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	5	5	82	82	829		
0	3	3	45	45	459		
0	3	3	45	45	458		
0	5	5	87	87	878		
0	2	2	61	61	618		
0	6	6	73	73	738		
0	6	6	74	74	749		
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a int, b int, INDEX idx(b));
CREATE TABLE t2 (a int, b int, INDEX idx(a));
INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20);
INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20);
INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20);
set join_buffer_size=32;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '32'
set join_cache_level=8;
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	Using join buffer (flat, BKAH join)
SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
a	b	a	b
7	40	7	10
7	40	7	10
7	40	7	20
8	30	8	10
8	30	8	20
DROP TABLE t1,t2;
#
# Bug #40134: outer join with not exists optimization and join buffer
#
set join_cache_level=default;
set join_buffer_size=default;
CREATE TABLE t1 (a int NOT NULL);
INSERT INTO t1 VALUES (2), (4), (3), (5), (1);
CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a));
INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20);
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
a	a	b
3	NULL	NULL
5	NULL	NULL
1	NULL	NULL
SET join_cache_level=6;
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (flat, BKA join)
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
a	a	b
3	NULL	NULL
5	NULL	NULL
1	NULL	NULL
DROP TABLE t1, t2;
set join_cache_level=default;
set join_buffer_size=default;
#
# BUG#40136: Group by is ignored when join buffer is used for an outer join
#
create table t1(a int PRIMARY KEY, b int);
insert into t1 values
(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
create table t2 (p int, a int, INDEX i_a(a));
insert into t2 values
(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
set @save_join_cache_level=@@join_cache_level;
set join_cache_level=6;
The following must not show "using join cache":
explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (flat, BKA join)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
a	count
1	1
2	0
3	2
5	0
6	0
7	2
8	0
9	0
set join_cache_level=@save_join_cache_level;
drop table t1, t2;
#
# BUG#40268: Nested outer join with not null-rejecting where condition
#            over an inner table which is not the last in the nest
#
CREATE TABLE t2 (a int, b int, c int);
CREATE TABLE t3 (a int, b int, c int);
CREATE TABLE t4 (a int, b int, c int);
INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
INSERT INTO t3 VALUES (1,2,0), (2,2,0);
INSERT INTO t4 VALUES (3,2,0), (4,2,0);
set join_cache_level=6;
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b
WHERE t3.a+2<t2.a OR t3.c IS NULL;
a	b	a	b	a	b
4	2	1	2	3	2
4	2	1	2	4	2
3	3	NULL	NULL	NULL	NULL
5	3	NULL	NULL	NULL	NULL
set join_cache_level=default;
DROP TABLE t2, t3, t4;
#
# Bug #40192: outer join with where clause when using BNL 
#
create table t1 (a int, b int);
insert into t1 values (2, 20), (3, 30), (1, 10);
create table t2 (a int, c int);
insert into t2 values (1, 101), (3, 102), (1, 100);
set join_cache_level=6;
select * from t1 left join t2 on t1.a=t2.a;
a	b	a	c
1	10	1	101
3	30	3	102
1	10	1	100
2	20	NULL	NULL
explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
1	SIMPLE	t2	hash	NULL	hj_key	5	test.t1.a	3	Using where; Using join buffer (flat, BNLH join)
select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
a	b	a	c
3	30	3	102
2	20	NULL	NULL
set join_cache_level=default;
drop table t1, t2;
#
# Bug #40317: outer join with with constant on expression equal to FALSE
#
create table t1 (a int);
insert into t1 values (30), (40), (20);
create table t2 (b int);
insert into t2 values (200), (100);
set join_cache_level=6;
select * from t1 left join t2 on (1=0);
a	b
30	NULL
40	NULL
20	NULL
explain select * from t1 left join t2 on (1=0) where a=40;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
select * from t1 left join t2 on (1=0) where a=40;
a	b
40	NULL
set join_cache_level=0;
explain select * from t1 left join t2 on (1=0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
set join_cache_level=default;
drop table t1, t2;
#
# Bug #41204: small buffer with big rec_per_key for ref access
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (0);
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1 VALUES (20000), (10000);
CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b));
INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5);
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
ANALYZE TABLE t1,t2;
set join_cache_level=6;
set join_buffer_size=1024;
EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2050	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	640	Using join buffer (flat, BKA join)
SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
AVG(c)
5.0000
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1, t2;
#
# Bug #41894: big join buffer of level 7 used to join records
#              with null values in place of varchar strings
#
CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY,
b varchar(127) DEFAULT NULL);
INSERT INTO t1(a) VALUES (1);
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
INSERT INTO t2 SELECT * FROM t1;
CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
INSERT INTO t3 SELECT * FROM t1;
set join_cache_level=7;
set join_buffer_size=1024*1024;
EXPLAIN
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join)
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
COUNT(*)
16384
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1,t2,t3;
#
# Bug #42020: join buffer is used  for outer join with fields of 
#             several outer tables in join buffer
#
CREATE TABLE t1 (
a bigint NOT NULL,
PRIMARY KEY (a) 
);
INSERT INTO t1 VALUES
(2), (1);
CREATE TABLE t2 (
a bigint NOT NULL,
b bigint NOT NULL,
PRIMARY KEY (a,b)
);
INSERT INTO t2 VALUES
(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
(1,10), (1, 20), (1,30), (1,40), (1,50);
CREATE TABLE t3 (
pk bigint NOT NULL AUTO_INCREMENT,
a bigint NOT NULL,
b bigint NOT NULL,
val bigint DEFAULT '0',
PRIMARY KEY (pk),
KEY idx (a,b)
);
INSERT INTO t3(a,b) VALUES
(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
(4,30), (4,40), (4,50), (4,60), (4,70), (4,80),
(5,30), (5,40), (5,50), (5,60), (5,70), (5,80),
(7,30), (7,40), (7,50), (7,60), (7,70), (7,80);
set join_cache_level=0;
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
set join_cache_level=6;
set join_buffer_size=256;
EXPLAIN
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
1	SIMPLE	t3	ref	idx	idx	16	test.t1.a,test.t2.b	2	Using join buffer (flat, BKA join)
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
DROP INDEX idx ON t3;
set join_cache_level=2;
EXPLAIN
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	24	Using where; Using join buffer (flat, BNL join)
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1,t2,t3;
create table t1(f1 int, f2 int);
insert into t1 values (1,1),(2,2),(3,3);
create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2));
insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
(2,4, 'qwerty'),(2,5, 'qwerty');
insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
(4,4, 'qwerty');
insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
(2,4, 'qwerty'),(2,5, 'qwerty');
insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
(4,4, 'qwerty');
set join_cache_level=5;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition(BKA); Using join buffer (flat, BKA join)
set join_cache_level=6;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition(BKA); Using join buffer (flat, BKA join)
set join_cache_level=7;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
set join_cache_level=8;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
f1	f2	f3
1	1	qwerty
2	2	qwerty
1	1	qwerty
2	2	qwerty
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
drop table t1,t2;
set join_cache_level=default;
#
# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 
#             
create table t1 (d int, id1 int, index idx1 (d, id1));
insert into t1 values
(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30);
create table t2 (id1 int, id2 int, index idx2 (id1));
insert into t2 values 
(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
(40, 200), (30, 300), (10, 400), (20, 200), (20, 300);
set join_cache_level=6;
explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 group by t1.id1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (flat, BKA join)
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 group by t1.id1;
id1	sum(t2.id2)
10	900
20	2000
30	900
explain
select t1.id1  from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (flat, BKA join)
select t1.id1  from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id1
10
10
20
20
20
20
30
30
set join_cache_level=default;
drop table t1,t2;
#
# Bug #44019: star-like multi-join query executed join_cache_level=6 
#             
create table t1 (a int, b int, c int, d int);
create table t2 (b int, e varchar(16), index idx(b));
create table t3 (d int, f varchar(16), index idx(d));
create table t4 (c int, g varchar(16), index idx(c));
insert into t1 values
(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000),
(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800),
(7, 70, 700, 7000);
insert into t2 values
(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
insert into t3 values
(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
insert into t4 values
(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
analyze table t2,t3,t4;
set join_cache_level=1;
explain 
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a	b	c	d	e	f	g
3	30	300	3000	bbb	ddd	ccc
set join_cache_level=6;
explain 
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	Using join buffer (flat, BKA join)
1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Using join buffer (incremental, BKA join)
1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Using join buffer (incremental, BKA join)
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a	b	c	d	e	f	g
3	30	300	3000	bbb	ddd	ccc
set join_cache_level=default;
drop table t1,t2,t3,t4;
#
# Bug #44250: Corruption of linked join buffers when using BKA 
#             
CREATE TABLE t1 (
id1 bigint(20) DEFAULT NULL,
id2 bigint(20) DEFAULT NULL,
id3 bigint(20) DEFAULT NULL,
num1 bigint(20) DEFAULT NULL,
num2 int(11) DEFAULT NULL,
num3 bigint(20) DEFAULT NULL
);
CREATE TABLE t2 (
id3 bigint(20) NOT NULL DEFAULT '0',
id4 bigint(20) DEFAULT NULL,
enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL,
PRIMARY KEY (id3)
);
CREATE TABLE t3 (
id4 bigint(20) NOT NULL DEFAULT '0',
text1 text,
PRIMARY KEY (id4)
);
CREATE TABLE t4 (
id2 bigint(20) NOT NULL DEFAULT '0',
dummy int(11) DEFAULT '0',
PRIMARY KEY (id2)
);
CREATE TABLE t5 (
id1 bigint(20) NOT NULL DEFAULT '0',
id2 bigint(20) NOT NULL DEFAULT '0',
enum2 enum('Active','Deleted','Paused') DEFAULT NULL,
PRIMARY KEY (id1,id2)
);
set join_cache_level=8;
set join_buffer_size=2048;
EXPLAIN
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where; Using join buffer (flat, BKAH join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where; Using join buffer (incremental, BKAH join)
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Using join buffer (incremental, BKAH join)
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t4.id2	1	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join)
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
id1	num3	text1	id4	id3	dummy
228172702	14	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	134	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	15	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	3	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228808822	61	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	60	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	6	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	18	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	17	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	50	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	89	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	19	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	9	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	84	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	14	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	10	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	26	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	28	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	62	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug#45267: Incomplete check caused wrong result.
#
CREATE TABLE t1 (
`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE t3 (
`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO t3 VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20);
CREATE TABLE t2 (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`time_key` time NOT NULL,
PRIMARY KEY (`pk`),
KEY `time_key` (`time_key`)
);
INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46');
SELECT DISTINCT t1.`pk`
FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey`  ON t2.`time_key`
GROUP BY 1;
pk
NULL
DROP TABLE IF EXISTS t1, t2, t3;
#
# Bug #46328: Use of aggregate function without GROUP BY clause 
#             returns many rows (vs. one )
#             
CREATE TABLE t1 (
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO t1 VALUES
(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9);
CREATE TABLE t2 (
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO t2 VALUES (2),(3);

# The query shall return 1 record with a max value 9 and one of the 
# int_key values inserted above (undefined which one). A changed 
# execution plan may change the value in the second column
SELECT  MAX(t1.int_key), t1.int_key
FROM t1 STRAIGHT_JOIN t2  
ORDER BY t1.int_key;
MAX(t1.int_key)	int_key
9	0

explain 
SELECT  MAX(t1.int_key), t1.int_key
FROM t1 STRAIGHT_JOIN t2  
ORDER BY t1.int_key;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	int_key	4	NULL	14	Using index
1	SIMPLE	t2	index	NULL	int_key	4	NULL	2	Using index; Using join buffer (flat, BNL join)

DROP TABLE t1,t2;
SET join_cache_level=default;
#
# Regression test for
# Bug#46733 - NULL value not returned for aggregate on empty result 
#             set w/ semijoin on
#
CREATE TABLE t1 (
i int(11) NOT NULL,
v varchar(1) DEFAULT NULL,
PRIMARY KEY (i)
);
INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d');
CREATE TABLE t2 (
i int(11) NOT NULL,
v varchar(1) DEFAULT NULL,
PRIMARY KEY (i)
);
INSERT INTO t2 VALUES (1,'x'),(2,'y');

SELECT MAX(t1.i) 
FROM t1 JOIN t2 ON t2.v
ORDER BY t2.v;
MAX(t1.i)
NULL

EXPLAIN
SELECT MAX(t1.i) 
FROM t1 JOIN t2 ON t2.v
ORDER BY t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index; Using join buffer (flat, BNL join)

DROP TABLE t1,t2;
#
# Bug #45092: join buffer contains two blob columns one of which is
#             used in the key employed to access the joined table
#
CREATE TABLE t1 (c1 int, c2 int, key (c2));
INSERT INTO t1 VALUES (1,1);
INSERT INTO t1 VALUES (2,2);
CREATE TABLE t2 (c1 text, c2 text);
INSERT INTO t2 VALUES('tt', 'uu');
INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
ANALYZE TABLE t1,t2;
set join_cache_level=6;
SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
2	2	tt	uu	2	2
set join_cache_level=default;
DROP TABLE t1,t2;
#
# Bug #51092: linked join buffer is used for a 3-way cross join query 
#             that selects only records of the first table
#
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table t2 (a int, b int);
insert into t2 values (1,1),(2,2);
create table t3 (a int, b int);
insert into t3 values (1,1),(2,2);
explain select t1.* from t1,t2,t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
select t1.* from t1,t2,t3;
a	b
1	1
2	2
1	1
2	2
1	1
2	2
1	1
2	2
set join_cache_level=2;
explain select t1.* from t1,t2,t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
select t1.* from t1,t2,t3;
a	b
1	1
2	2
1	1
2	2
1	1
2	2
1	1
2	2
set join_cache_level=default;
drop table t1,t2,t3;
#
# Bug #52394:  using join buffer for 3 table join with ref access 
# LP #623209: and no references to the columns of the middle table
#
set join_cache_level=6;
CREATE TABLE t1 (a int(11), b varchar(1));
INSERT INTO t1 VALUES (6,'r'),(27,'o');
CREATE TABLE t2(a int);
INSERT INTO t2 VALUES(1),(2),(3),(4),(5);
CREATE TABLE t3 (a int(11) primary key, b varchar(1));
INSERT INTO t3 VALUES
(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),
(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'o');
EXPLAIN
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (incremental, BKA join)
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
a
27
27
27
27
27
DROP TABLE t1,t2,t3;
set join_cache_level=default;
#
# Bug #51084: Batched key access crashes for SELECT with
#             derived table and LEFT JOIN 
#
CREATE TABLE t1 (
carrier int,
id int PRIMARY KEY
);
INSERT INTO t1 VALUES (1,11),(1,12),(2,13);
CREATE TABLE t2 (
scan_date int,
package_id int
);
INSERT INTO t2 VALUES (2008,21),(2008,22);
CREATE TABLE t3 (
carrier int PRIMARY KEY,
id int
);
INSERT INTO t3 VALUES (1,31);
CREATE TABLE t4 (
carrier_id int,
INDEX carrier_id(carrier_id)
);
INSERT INTO t4 VALUES (31),(32);
SET join_cache_level=8;
SELECT COUNT(*)
FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
ON t3.carrier = t1.carrier;
COUNT(*)
6
EXPLAIN
SELECT COUNT(*)
FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
ON t3.carrier = t1.carrier;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	Using where
1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
SET join_cache_level=default;
DROP TABLE t1,t2,t3,t4;
#
# Bug #52636: allowing JOINs on NULL values w/ join_cache_level = 5-8
#
CREATE TABLE t1 (b int);
INSERT INTO t1 VALUES (NULL),(3);
CREATE TABLE t2 (a int, b int, KEY (b));
INSERT INTO t2 VALUES (100,NULL),(150,200);
set join_cache_level = 5;
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKA join)
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level = 8;
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKAH join)
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
delete from t1;
INSERT INTO t1 VALUES (NULL),(NULL);
set join_cache_level = 5;
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKA join)
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
DROP TABLE t1,t2;
CREATE TABLE t1 (b varchar(100));
INSERT INTO t1 VALUES (NULL),("some varchar");
CREATE TABLE t2 (a int, b varchar(100), KEY (b));
INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
set join_cache_level = 5;
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using where; Using join buffer (flat, BKA join)
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level = 8;
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using where; Using join buffer (flat, BKAH join)
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level = default;
DROP TABLE t1,t2;
#
# Bug #54359: Extra rows with join_cache_level=7,8 and two joins
#             and multi-column index"
#
CREATE TABLE t1 (
pk int NOT NULL,
a int DEFAULT NULL,
b varchar(16) DEFAULT NULL,
c varchar(16) DEFAULT NULL,
INDEX idx (b,a))
;
INSERT INTO t1 VALUES (4,9,'k','k');
INSERT INTO t1 VALUES (12,5,'k','k');
set join_cache_level = 8;
EXPLAIN
SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
WHERE s.pk AND s.a  >= t.pk AND  s.b  = t.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	s	ref	idx	idx	19	test.t.c	1	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
WHERE s.pk AND s.a  >= t.pk AND  s.b  = t.c;
a
9
9
set join_cache_level = default;
DROP TABLE t1;
#
# Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs
#
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);
INSERT INTO t1 VALUES (null), (2), (null), (1);
set join_cache_level = 6;
EXPLAIN
SELECT t1.a 
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 
WHERE t1.a OR t3.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
SELECT t1.a 
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 
WHERE t1.a OR t3.a;
a
2
1
EXPLAIN
SELECT t1.a 
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
SELECT t1.a 
FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0
WHERE t1.a OR t4.a;
a
2
1
set join_cache_level = default;
DROP TABLE t1,t2,t3,t4;
#
# Bug #663840: Memory overwrite causing crash with hash join
#
SET SESSION join_cache_level=3;
SET SESSION join_buffer_size=100;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '100'
CREATE TABLE t3 (
i int NOT NULL,
j int NOT NULL,
d date NOT NULL,
t time NOT NULL,
v varchar(1) NOT NULL,
u varchar(1) NOT NULL,
INDEX idx (v)
)  COLLATE=latin1_bin;
INSERT INTO t3 VALUES
(3,8,'2008-12-04','00:00:00','v','v'), (3,8,'2009-03-28','00:00:00','f','f'),
(3,5,'1900-01-01','00:55:47','v','v'), (2,8,'2009-10-02','00:00:00','s','s'),
(1,8,'1900-01-01','20:51:59','a','a'), (0,6,'2008-06-04','09:47:27','p','p'),
(8,7,'2009-01-13','21:58:29','z','z'), (5,2,'1900-01-01','22:45:53','a','a'),
(9,5,'2008-01-28','14:06:48','h','h'), (5,7,'2004-09-18','22:17:16','h','h'),
(4,2,'2006-10-14','14:59:37','v','v'), (2,9,'1900-01-01','23:37:40','v','v'),
(33,142,'2000-11-28','14:14:01','b','b'), (5,3,'2008-04-04','02:54:19','y','y'),
(1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'),
(1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'),
(8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a');
CREATE TABLE t1 SELECT * FROM t3;
DELETE FROM t1 WHERE i > 8;
CREATE TABLE t2 SELECT * FROM t3;
DELETE FROM t2 WHERE j > 10;
EXPLAIN
SELECT t1.i, t1.d,  t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3
WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash	idx	idx	3	test.t2.u	20	Using where; Using join buffer (flat, BNLH join)
SELECT t1.i, t1.d,  t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3
WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u;
i	d	v	i	d	t	v
0	2008-06-04	p	5	1900-01-01	22:45:53	a
0	2008-06-04	p	5	1900-01-01	22:45:53	a
0	2008-06-04	p	5	1900-01-01	22:45:53	a
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2006-04-02	z	5	1900-01-01	22:45:53	a
1	2006-04-02	z	5	1900-01-01	22:45:53	a
1	2006-04-02	z	5	1900-01-01	22:45:53	a
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	2009-10-02	s	5	1900-01-01	22:45:53	a
2	2009-10-02	s	5	1900-01-01	22:45:53	a
2	2009-10-02	s	5	1900-01-01	22:45:53	a
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2009-03-28	f	5	1900-01-01	22:45:53	a
3	2009-03-28	f	5	1900-01-01	22:45:53	a
3	2009-03-28	f	5	1900-01-01	22:45:53	a
4	2006-10-14	v	5	1900-01-01	22:45:53	a
4	2006-10-14	v	5	1900-01-01	22:45:53	a
4	2006-10-14	v	5	1900-01-01	22:45:53	a
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2008-04-04	y	5	1900-01-01	22:45:53	a
5	2008-04-04	y	5	1900-01-01	22:45:53	a
5	2008-04-04	y	5	1900-01-01	22:45:53	a
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2009-01-13	z	5	1900-01-01	22:45:53	a
8	2009-01-13	z	5	1900-01-01	22:45:53	a
8	2009-01-13	z	5	1900-01-01	22:45:53	a
DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=DEFAULT;
SET SESSION join_buffer_size=DEFAULT;
#
# Bug #664508: 'Simple' GROUP BY + ORDER BY 
#              when join buffers are used
#
CREATE TABLE t1 (
pk int NOT NULL, i int NOT NULL, v  varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2 (v,i)
) COLLATE latin1_bin;
INSERT INTO t1 VALUES
(10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
(15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
(25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
CREATE TABLE t2 (
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
) COLLATE latin1_bin;
INSERT INTO t2 VALUES
(10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
(15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
(20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'),
(25,3,'m'), (26,5,'z'), (27,9,'n'), (28,1,'d'), (29,107,'a');
CREATE TABLE t3 (
pk int NOT NULL, i int NOT NULL,  v varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
) COLLATE latin1_bin;
INSERT INTO t3 VALUES
(1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
(6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
(11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'),
(16,6,'e'), (17,3,'t'), (18,8,'j'), (19,5,'h'), (20,7,'w');
SET SESSION join_cache_level=1;
EXPLAIN
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	2	
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
h
z
p
n
v
SET SESSION join_cache_level=6;
EXPLAIN
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	idx1	NULL	NULL	NULL	20	Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where; Using join buffer (flat, BKA join)
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	2	Using join buffer (incremental, BKA join)
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
h
z
n
v
p
SET SESSION join_cache_level=4;
EXPLAIN
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	idx1	NULL	NULL	NULL	20	Using temporary; Using filesort
1	SIMPLE	t3	hash	PRIMARY,idx2	PRIMARY	4	test.t2.i	20	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t1	hash	idx2	idx2	3	test.t3.v	15	Using join buffer (incremental, BNLH join)
SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
h
z
n
v
p
DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=DEFAULT;
#
# Bug #668290: hash join with non-binary collations 
#
CREATE TABLE t1 (
i int DEFAULT NULL,
cl varchar(10) CHARACTER SET latin1 DEFAULT NULL,
cu varchar(10) CHARACTER SET utf8 DEFAULT NULL,
INDEX cl (cl),
INDEX cu (cu)
);
INSERT INTO t1 VALUES
(650903552,'cmxffkpsel','z'), (535298048,'tvtjrcmxff','y'),
(1626865664,'when','for'), (39649280,'rcvljitvtj','ercvljitvt'),
(792068096,'ttercvljit','jttercvlji');
INSERT INTO t1 SELECT * FROM t1;
CREATE TABLE t2 (
cu varchar(10) CHARACTER SET utf8 DEFAULT NULL,
i int DEFAULT NULL,
cl varchar(10) CHARACTER SET latin1 DEFAULT NULL,
INDEX cu (cu),
INDEX cl (cl)
);
INSERT INTO t2 VALUES 
('g',7,'like'), ('fujttercvl',6,'y'),
('s',2,'e'), ('didn\'t',0,'v'),
  ('gvdrodpedk',8,'chogvdrodp'), ('jichogvdro',7,'will');
EXPLAIN
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
1	SIMPLE	t1	ref	cu	cu	33	func	2	Using where; Using index
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
i
6
6
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
1	SIMPLE	t1	hash	cu	cu	33	func	10	Using where; Using index; Using join buffer (flat, BNLH join)
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
i
6
6
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #669382: hash join using a ref with constant key parts 
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES
(9), (11), (7), (8), (4), (1), (12), (3), (5);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
CREATE TABLE t2 (a int, b int, c int, INDEX idx (a,b));
INSERT INTO t2 VALUES
(8, 80, 800), (1, 10, 100), (1, 11, 101), (3, 30, 300),
(1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200),
(8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301),
(1, 15, 105), (8, 83, 803), (7, 71, 701);
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 192;
EXPLAIN
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	36	Using where
1	SIMPLE	t2	hash	idx	idx	10	test.t1.a,const	15	Using join buffer (flat, BNLH join)
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
a	c
SET SESSION join_cache_level = DEFAULT;
SET SESSION join_buffer_size = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #671901: hash join using a ref to a varchar field
#
CREATE TABLE t1 (
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
i int  DEFAULT NULL
);
INSERT INTO t1 VALUES
('k',8), ('abcdefjh',-575340544), ('f',77), ('because', 2), ('f',-517472256),
('abcdefjhj',5), ('z',7);
CREATE TABLE t2 (
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
i int DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t2 VALUES
('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), 
('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888),
('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464),
('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 
('ff', 5), ('abcdefjhjk',-1074397184);
EXPLAIN 
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	ref	idx	idx	13	test.t1.v	2	
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
v	i
f	4
f	4
EXPLAIN
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
1	SIMPLE	t2	ref	idx	idx	13	func	2	Using index condition
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
v	i
f	5
f	5
SET SESSION join_cache_level = 4;
EXPLAIN 
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	hash	idx	idx	13	test.t1.v	18	Using join buffer (flat, BNLH join)
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
v	i
f	4
f	4
EXPLAIN
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
1	SIMPLE	t2	hash	idx	idx	13	func	18	Using where; Using join buffer (flat, BNLH join)
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
v	i
f	5
f	5
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
# Bug #672497: 3 way join with tiny incremental join buffer with
#              and a ref access from the first table 
#
CREATE TABLE t1 (
pk int PRIMARY KEY,
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t1 VALUES
(1,'abcdefjhjk'), (2,'i'),(3,'abcdefjhjk'), (4,'well'), (5,'abcdefjhjk'),
(6,'abcdefjhjk'), (7,'that');
CREATE TABLE t2 (
pk int PRIMARY KEY,
i int DEFAULT NULL,
v varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t2 VALUES 
(1,6,'yes'), (2,NULL,'will'), (3,NULL,'o'), (4,NULL,'k'), (5,NULL,'she'),
(6,-1450835968,'abcdefjhjkl'), (7,-975831040,'abcdefjhjkl'), (8,NULL,'z'),
(10,-343932928,'t'),
(11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'),
(16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'),
(19,-343932928,'t');
CREATE TABLE t3 (
pk int NOT NULL PRIMARY KEY,
i int,
v varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
INDEX idx (v(333))
);
INSERT INTO t3 VALUES 
(1,7,'abcdefjhjkl'),(2,6,'y'), (3,NULL,'to'),(4,7,'n'),(5,7,'look'), (6,NULL,'all'),
(7,1443168256,'c'), (8,1427046400,'right'),
(11,7,'abcdefjhjkl'), (12,6,'y'), (13,NULL,'to'), (14,7,'n'), (15,7,'look'),
(16,NULL,'all'), (17,1443168256,'c'), (18,1427046400,'right'),
(21,7,'abcdefjhjkl'), (22,6,'y'), (23,NULL,'to'), (24,7,'n'), (25,7,'look'),
(26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'),
(31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'),
(36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right');
SET SESSION join_buffer_size = 192;
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT t3.i FROM t1,t2,t3
WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	idx	idx	13	NULL	7	Using where; Using index
1	SIMPLE	t2	hash	idx	idx	1003	test.t1.v	18	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t3	hash	idx	idx	1002	func	32	Using where; Using join buffer (incremental, BNLH join)
SELECT t3.i FROM t1,t2,t3
WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;
i
SET SESSION join_cache_level = DEFAULT;
SET SESSION join_buffer_size = DEFAULT;
DROP TABLE t1,t2,t3;
#
# Bug #672551: hash join over a long varchar field
#
CREATE TABLE t1 (
pk int PRIMARY KEY,
a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (a)
);
INSERT INTO t1 VALUES (2, 'aa'), (5, 'ccccccc'), (3, 'bb');
CREATE TABLE t2(
pk int PRIMARY KEY,
a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (a)
);
INSERT INTO t2 VALUES 
(10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'),
(11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'),
(12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa');
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
pk	a	pk	a
2	aa	30	aa
2	aa	31	aa
2	aa	42	aa
3	bb	4	bb
3	bb	32	bb
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	hash	idx	idx	515	test.t1.a	12	Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
pk	a	pk	a
2	aa	30	aa
3	bb	4	bb
2	aa	31	aa
3	bb	32	bb
2	aa	42	aa
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #674431: nested outer join when join_cache_level is set to 7
#
CREATE TABLE t1 (a int, b varchar(32)) ;
INSERT INTO t1 VALUES (5,'h'), (NULL,'j');
CREATE TABLE t2 (a int, b varchar(32), c int) ;
INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200);
CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b));
INSERT INTO t3 VALUES (77,'h'), (88,'g');
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 7;
SELECT t3.a
FROM t1 LEFT JOIN
(t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b
WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c;
a
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3;
#
# Bug #52540: nested outer join when join_cache_level is set to 3
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (a varchar(10));
INSERT INTO t2 VALUES ('f'),('x');
CREATE TABLE t3 (pk int(11) PRIMARY KEY);
INSERT INTO t3 VALUES (2);
CREATE TABLE t4 (a varchar(10));
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 3;
SELECT *
FROM t2 LEFT JOIN
((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1;
a	a	pk	a
f	2	2	NULL
x	2	2	NULL
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3,t4;
#
# Bug #674423: outer join with ON expression over only outer tables 
#
CREATE TABLE t1 (a int) ;
INSERT INTO t1 VALUES ('9');
CREATE TABLE t2 (pk int, a int) ;
INSERT INTO t2 VALUES ('9',NULL), ('1',NULL);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9;
pk	a	a
1	NULL	NULL
SET SESSION join_cache_level = 1;
EXPLAIN
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
pk	a	a
1	NULL	NULL
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #675095: nested outer join using join buffer 
#
CREATE TABLE t1 (pk int, a1 int) ;
INSERT IGNORE INTO t1 VALUES (2,NULL), (8,0);
CREATE TABLE t2 (pk int, a2 int, c2 int, d2 int) ;
INSERT IGNORE INTO t2 VALUES  (9,0,0,2), (1,0,0,7);
CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ;
INSERT IGNORE INTO t3 VALUES  (9,0,0,2), (1,0,0,7);
CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ;
INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0);
CREATE TABLE t5 (pk int, a5 int) ;
INSERT IGNORE INTO t5 VALUES (2,0), (8,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`d2` = `test`.`t1`.`pk`) and (`test`.`t3`.`a3` = `test`.`t2`.`c2`))) left join `test`.`t4` on(((`test`.`t4`.`a4` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` is not null))) left join `test`.`t5` on((`test`.`t5`.`a5` = `test`.`t3`.`a3`)) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION join_cache_level = 2;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`d2` = `test`.`t1`.`pk`) and (`test`.`t3`.`a3` = `test`.`t2`.`c2`))) left join `test`.`t4` on(((`test`.`t4`.`a4` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` is not null))) left join `test`.`t5` on((`test`.`t5`.`a5` = `test`.`t3`.`a3`)) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION join_cache_level = 1;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`d2` = `test`.`t1`.`pk`) and (`test`.`t3`.`a3` = `test`.`t2`.`c2`))) left join `test`.`t4` on(((`test`.`t4`.`a4` = `test`.`t1`.`a1`) and (`test`.`t1`.`a1` is not null))) left join `test`.`t5` on((`test`.`t5`.`a5` = `test`.`t3`.`a3`)) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug #675516: nested outer join with 3 tables in the nest
#             using BNL + BNLH 
#
CREATE TABLE t1 (a1 int, b1 int, c1 int) ;
INSERT INTO t1 VALUES (7,8,0), (6,4,0);
CREATE TABLE t2 (a2 int) ;
INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ;
INSERT INTO t3 VALUES (2,5,0);
CREATE TABLE t4 (a4 int, b4 int, c4 int) ;
INSERT INTO t4 VALUES (7,8,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	hash	PRIMARY	hj_key	5	test.t1.c1	1	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t2	hash	NULL	hj_key	5	test.t3.b3	1	Using where; Using join buffer (incremental, BNLH join)
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3,t4;
#
# Bug #660963: nested outer join with join_cache_level set to 5
#
CREATE TABLE t1 (a1 int) ;
INSERT INTO t1 VALUES (0),(0);
CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ;
INSERT INTO t2 VALUES (2,1);
CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ;
INSERT INTO t3 VALUES (2,1);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 6;
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join)
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
a1	a2	b2	a3	b3
0	2	1	2	1
0	2	1	2	1
SET SESSION join_cache_level = 5;
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
a1	a2	b2	a3	b3
0	2	1	2	1
0	2	1	2	1
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3;
#
# Bug #675922: incremental buffer for BKA with access from previous
#      buffers from non-nullable columns whose values may be null
#
CREATE TABLE t1 (a1 varchar(32)) ;
INSERT INTO t1 VALUES ('s'),('k');
CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
INSERT INTO t2 VALUES (7,'s');
CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
INSERT INTO t3 VALUES (7,'s');
CREATE TABLE t4 (a4 int) ;
INSERT INTO t4 VALUES (9);
CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
INSERT INTO t5 VALUES (7,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4	b5
9	0
9	NULL
SET SESSION join_cache_level = 6;
EXPLAIN
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join)
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4	b5
9	0
9	NULL
SET SESSION optimizer_switch = 'outer_join_with_cache=off';
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug #670380: hash join for non-binary collation
#
CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32));
CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a));
INSERT INTO t1 VALUES
(10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee');
INSERT INTO t2 VALUES
(1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'),
(6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'),
(11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D');
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM t1,t2 WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
1	SIMPLE	t2	hash	idx	idx	35	test.t1.a	15	Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t1.a=t2.a;
pk	a	pk	a
20	BBBB	1	Bbbb
20	BBBB	3	bbbb
10	AAA	4	AaA
30	Cc	5	CC
30	Cc	6	cC
10	AAA	8	AAA
20	BBBB	9	bBbB
40	DD	12	dd
50	ee	13	EE
50	ee	14	ee
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #694092: incorrect detection of index only pushdown conditions
#
CREATE TABLE t1 (
f1 varchar(10), f3 int(11), PRIMARY KEY (f3)
);
INSERT INTO t1 VALUES ('y',1),('or',5);
CREATE TABLE t2 (
f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3)
);
INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m');
SET SESSION join_cache_level = 1;
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using MRR
1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using MRR; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
f1	f3	f3	f2	f4
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR
1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using MRR; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
f1	f3	f3	f2	f4
SET SESSION join_cache_level = DEFAULT;
SET SESSION optimizer_switch = DEFAULT;
DROP TABLE t1,t2;
CREATE TABLE t1 (f1 int, f2 varchar(10), KEY (f1), KEY (f2)) ;
INSERT INTO t1 VALUES
(4,'e'), (891879424,'l'), (-243400704,'ectlyqupbk'), (1851981824,'of'),
(-1495203840,'you'), (4,'no'), (-1436942336,'c'), (891420672,'DQQYO'),
(608698368,'qergldqmec'), (1,'x');
CREATE TABLE t2 (f3 varchar(64), KEY (f3));
INSERT INTO t2 VALUES 
('d'), ('UALLN'), ('d'), ('z'), ('r'), ('YVAKV'), ('d'), ('TNGZK'), ('e'),
('xucupaxdyythsgiw'), ('why'), ('ttugkxucupaxdyyt'), ('l'), ('LHTKN'),
('d'), ('o'), ('v'), ('KGLCJ'), ('your');
SET SESSION optimizer_switch='index_merge_sort_intersection=off';
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	f1,f2	f2	13	NULL	10	Using where
1	SIMPLE	t2	ref	f3	f3	67	test.t1.f2	2	Using where; Using index
SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
f1	f2	f3
SET SESSION optimizer_switch = DEFAULT;
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	f1,f2	f2	13	NULL	10	Using where
1	SIMPLE	t2	ref	f3	f3	67	test.t1.f2	2	Using where; Using index
SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
f1	f2	f3
SET SESSION optimizer_switch = DEFAULT;
DROP TABLE t1,t2;
#
# Bug #694443: hash join using IS NULL the an equi-join condition
#
CREATE TABLE t1 (a int PRIMARY KEY);
INSERT INTO t1 VALUES
(7), (4), (9), (1), (3), (8), (2);
CREATE TABLE t2 (a int, b int, INDEX idx (a));
INSERT INTO t2 VALUES
(NULL,10), (4,80), (7,70), (6,11), (7,90), (NULL,40),
(4,77), (4,50), (NULL,41), (7,99), (7,88), (8,12),
(1,21), (4,90), (7,91), (8,22), (6,92), (NULL,42),
(2,78), (2,51), (1,43), (5,97), (5,89);
SET SESSION join_cache_level = 1;
EXPLAIN 
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
1	SIMPLE	t2	ref	idx	idx	5	const	4	Using index condition
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
a	a	b
1	NULL	10
1	NULL	40
1	NULL	41
1	NULL	42
2	NULL	10
2	NULL	40
2	NULL	41
2	NULL	42
SET SESSION join_cache_level = 4;
EXPLAIN 
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using index
1	SIMPLE	t2	hash	idx	idx	5	const	23	Using where; Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
a	a	b
1	NULL	10
2	NULL	10
1	NULL	40
2	NULL	40
1	NULL	41
2	NULL	41
1	NULL	42
2	NULL	42
SET SESSION join_cache_level = DEFAULT;
DROP TABLE t1,t2;
set @@optimizer_switch=@save_optimizer_switch;