diff options
Diffstat (limited to 'mysql-test/r/join_cache.result')
| -rw-r--r-- | mysql-test/r/join_cache.result | 3999 |
1 files changed, 2409 insertions, 1590 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 4e0ae5bf640..0e616e259bc 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -1,5 +1,12 @@ 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='optimize_join_buffer_size=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='outer_join_with_cache=on'; +set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; set names utf8; CREATE DATABASE world; use world; @@ -42,7 +49,7 @@ 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 +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; @@ -68,158 +75,48 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer +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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 @@ -230,7 +127,7 @@ 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 +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; @@ -256,158 +153,306 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer +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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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_ALL NULL #hash#$hj 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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_ALL NULL #hash#$hj 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock United States English +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +CREATE INDEX City_Population ON City(Population); +CREATE INDEX City_Name ON City(Name); +ANALYZE TABLE City; +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND 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 City hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 25 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND 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 City hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +DROP INDEX City_Population ON City; +DROP INDEX City_Name ON City; set join_cache_level=default; set join_buffer_size=256; show variables like 'join_buffer_size'; @@ -422,7 +467,7 @@ 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 +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; @@ -448,158 +493,48 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer +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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 @@ -610,7 +545,85 @@ 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 +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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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_ALL NULL #hash#$hj 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; @@ -636,158 +649,126 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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_ALL NULL #hash#$hj 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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'; @@ -827,6 +808,465 @@ 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; Rowid-ordered scan +1 SIMPLE City hash_ALL Population,Country #hash#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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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; Rowid-ordered scan +1 PRIMARY City hash_ALL Population,Country #hash#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_ALL PRIMARY #hash#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; Rowid-ordered scan +1 SIMPLE City hash_ALL Population,Country #hash#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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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; Rowid-ordered scan +1 PRIMARY City hash_ALL Population,Country #hash#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_ALL PRIMARY #hash#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 +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 52 NULL # Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +CREATE INDEX City_Name ON City(Name); +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 52 NULL 17 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +DROP INDEX City_Name ON City; +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 @@ -836,8 +1276,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -863,173 +1303,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1057,7 +1379,7 @@ 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 +1 SIMPLE CountryLanguage eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1151,8 +1473,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1178,173 +1500,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1372,7 +1576,7 @@ 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 +1 SIMPLE CountryLanguage eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1466,8 +1670,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1493,173 +1697,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1687,7 +1773,7 @@ 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 +1 SIMPLE CountryLanguage eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1781,8 +1867,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1808,173 +1894,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2002,7 +1970,7 @@ 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 +1 SIMPLE CountryLanguage eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -2091,6 +2059,214 @@ 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; Rowid-ordered scan +1 SIMPLE City hash_ALL Population,Country #hash#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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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; Rowid-ordered scan +1 PRIMARY City hash_ALL Population,Country #hash#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; Rowid-ordered scan +1 SIMPLE City hash_ALL Population,Country #hash#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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +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_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 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 AND +LENGTH(Language) < LENGTH(City.Name) - 2; +Name Name Language +La Matanza Argentina Spanish +Lomas de Zamora Argentina Spanish +Lauro de Freitas Brazil Portuguese +Los Angeles Chile Spanish +Las Palmas de Gran Canaria Spain Spanish +L´Hospitalet de Llobregat Spain Spanish +Lleida (Lérida) Spain Spanish +Liupanshui China Chinese +Lianyungang China Chinese +Liangcheng China Chinese +Lengshuijiang China Chinese +Lázaro Cárdenas Mexico Spanish +Lagos de Moreno Mexico Spanish +Las Margaritas Mexico Spanish +Lashio (Lasho) Myanmar Burmese +Lalitapur Nepal Nepali +Ludwigshafen am Rhein Germany German +Leverkusen Germany German +Luchou Taiwan Min +Lungtan Taiwan Min +Lower Hutt New Zealand English +Los Teques Venezuela Spanish +Leninsk-Kuznetski Russian Federation Russian +Los Angeles United States English +Long Beach United States English +Lexington-Fayette United States English +Louisville United States English +Little Rock 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; Rowid-ordered scan +1 PRIMARY City hash_ALL Population,Country #hash#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 @@ -2100,8 +2276,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2127,173 +2303,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2322,8 +2380,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2349,173 +2407,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2544,8 +2484,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2571,173 +2511,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2766,8 +2588,8 @@ 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 +1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2793,173 +2615,55 @@ 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan 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; +CountryLanguage.Percentage > 50 AND +LENGTH(Language) < LENGTH(City.Name) - 2; 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 -# !!!NB igor: after backporting the SJ code the following should return -# 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 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 +1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Rowid-ordered scan +1 PRIMARY City ref Population,Country Country 3 world.Country.Code 18 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -3038,62 +2742,62 @@ Ho Chi Minh City Vietnam New York United States Los Angeles United States set join_cache_level=8; -set join_buffer_size=256; +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 +1 SIMPLE City range Population,Country Population 4 NULL # Using index condition; Rowid-ordered scan +1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.City.Country # Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; Name Name -Sydney Australia -Dhaka Bangladesh -Rio de Janeiro Brazil -São Paulo Brazil -London United Kingdom -Santiago de Chile Chile Alexandria Egypt +Ankara Turkey +Baghdad Iraq +Bangkok Thailand +Berlin Germany Cairo Egypt -Jakarta Indonesia -Delhi India Calcutta [Kolkata] India -Mumbai (Bombay) India +Chengdu China Chennai (Madras) India -Baghdad Iraq -Teheran Iran -Tokyo Japan -Jokohama [Yokohama] Japan -Peking China Chongqing China -Shanghai China -Wuhan China +Ciudad de México Mexico +Delhi India +Dhaka Bangladesh Harbin China -Shenyang China +Ho Chi Minh City Vietnam +Istanbul Turkey +Jakarta Indonesia +Jokohama [Yokohama] Japan Kanton [Guangzhou] China -Tianjin 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 +Kinshasa Congo, The Democratic Republic of the Lahore Pakistan Lima Peru -Berlin Germany +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 -Bangkok Thailand -Ankara Turkey -Istanbul Turkey St Petersburg Russian Federation -Moscow Russian Federation -Ho Chi Minh City Vietnam -Los Angeles United States -New York United States +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 ''; @@ -3333,15 +3037,15 @@ 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 -1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer +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); Key-ordered Rowid-ordered scan +1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Using join buffer -1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer -1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer -1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer -1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer +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); Key-ordered Rowid-ordered scan +1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t1.uniquekey, t1.xml AS affiliateXml, t8.name AS artistName, t8.artistid, t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, @@ -3392,7 +3096,7 @@ 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 +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, @@ -3420,9 +3124,9 @@ 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 +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 +1 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 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; @@ -3454,7 +3158,7 @@ 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 +1 SIMPLE t2 ref idx idx 5 test.t1.a 2 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; a b a b 7 40 7 10 @@ -3487,7 +3191,7 @@ 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 +1 SIMPLE t2 ref i_a i_a 4 test.t1.a 2 Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; a a b 3 NULL NULL @@ -3514,7 +3218,7 @@ 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 +1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 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 @@ -3566,7 +3270,7 @@ a b a c 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 ALL NULL NULL NULL NULL 3 Using where; Using join buffer +1 SIMPLE t2 hash_ALL NULL #hash#$hj 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 @@ -3589,11 +3293,11 @@ a b 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 +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=1; +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 @@ -3632,8 +3336,8 @@ 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 -1 SIMPLE t2 ref idx idx 5 test.t1.a 640 Using join buffer +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); Key-ordered Rowid-ordered scan SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; AVG(c) 5.0000 @@ -3673,8 +3377,8 @@ 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 -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan 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; @@ -3714,6 +3418,7 @@ INSERT INTO t3(a,b) VALUES (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; @@ -3738,7 +3443,7 @@ 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 +1 SIMPLE t3 ref idx idx 16 test.t1.a,test.t2.b 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 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; @@ -3755,7 +3460,7 @@ a a a b b val 2 2 2 70 70 0 2 2 2 80 80 0 DROP INDEX idx ON t3; -set join_cache_level=4; +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) @@ -3763,7 +3468,7 @@ 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 +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; @@ -3808,8 +3513,8 @@ f1 f2 f3 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 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer +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); Key-ordered Rowid-ordered scan 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; @@ -3821,8 +3526,8 @@ f1 f2 f3 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 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer +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); Key-ordered Rowid-ordered scan 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; @@ -3834,8 +3539,8 @@ f1 f2 f3 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 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer +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); Key-ordered Rowid-ordered scan 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; @@ -3847,8 +3552,8 @@ f1 f2 f3 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 -1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer +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); Key-ordered Rowid-ordered scan drop table t1,t2; set join_cache_level=default; # @@ -3866,8 +3571,8 @@ 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 index; Using temporary; Using filesort -1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer +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); Key-ordered Rowid-ordered scan 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) @@ -3878,8 +3583,8 @@ 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 index; Using temporary; Using filesort -1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer +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); Key-ordered Rowid-ordered scan 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 @@ -3922,7 +3627,7 @@ 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 +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 @@ -3935,10 +3640,10 @@ 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 -1 SIMPLE t2 ref idx idx 5 test.t1.b 1 Using join buffer -1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Using join buffer -1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Using join buffer +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); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 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 @@ -3986,20 +3691,20 @@ 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 -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using join buffer -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where; Using join buffer -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Using join buffer -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Using join buffer +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); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan 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 -228172702 134 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2567095402 2667134182 0 228808822 61 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 13 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 60 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 @@ -4007,17 +3712,17 @@ id1 num3 text1 id4 id3 dummy 228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 -228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 -228808822 50 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 9 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 10 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 26 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 @@ -4087,7 +3792,7 @@ 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 +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; @@ -4115,8 +3820,8 @@ ORDER BY t2.v; MAX(t1.i) NULL Warnings: -Warning 1292 Truncated incorrect INTEGER value: 'x' -Warning 1292 Truncated incorrect INTEGER value: 'y' +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'y' EXPLAIN SELECT MAX(t1.i) @@ -4124,7 +3829,7 @@ 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 +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index; Using join buffer (flat, BNL join) DROP TABLE t1,t2; # @@ -4158,8 +3863,8 @@ 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 -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +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 @@ -4174,8 +3879,8 @@ 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 -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using join buffer +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 @@ -4188,3 +3893,1117 @@ a b 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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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); Key-ordered Rowid-ordered scan +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_ALL idx #hash#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); Key-ordered Rowid-ordered scan +1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 2 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +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_ALL PRIMARY,idx2 #hash#PRIMARY 4 test.t2.i 20 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL idx2 #hash#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_index cu #hash#cu:cu 33:33 func 10 Using where; 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 = 256; +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_ALL idx #hash#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_ALL idx #hash#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_ALL idx #hash#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 = 256; +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_ALL idx #hash#idx 1003 test.t1.v 18 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL idx #hash#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_ALL idx #hash#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_ALL PRIMARY #hash#$hj 5 test.t1.c1 1 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 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); Key-ordered Rowid-ordered scan +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_ALL PRIMARY #hash#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); Key-ordered Rowid-ordered scan +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_ALL idx #hash#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; Rowid-ordered scan +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Rowid-ordered scan; 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; Rowid-ordered scan +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; 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 = @local_join_cache_test_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 = @local_join_cache_test_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 = @local_join_cache_test_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_range idx #hash#idx:idx 5:5 const 4 Using index condition; Rowid-ordered scan; 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; +# +# Bug #697557: hash join on a varchar field +# +CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t1 VALUES ('r',1), ('m',2); +CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t2 VALUES +('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), +('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), +('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77); +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL f1 NULL NULL NULL 2 Using where +1 SIMPLE t2 hash_ALL f1 #hash#f1 13 test.t1.f1 12 Using join buffer (flat, BNLH join) +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +f1 f2 f1 f2 +SET SESSION join_cache_level = DEFAULT; +DROP TABLE t1,t2; +# +# Bug #707827: hash join on varchar column with NULLs +# +CREATE TABLE t1 (v varchar(1)); +INSERT INTO t1 VALUES ('o'), ('u'); +CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; +INSERT INTO t2 VALUES +(8,NULL), (10,'b'), (5,'k'), (4,NULL), +(1,NULL), (11,'u'), (7,NULL), (2,'d'); +SET SESSION join_buffer_size = 256; +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT a 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 2 Using where +1 SIMPLE t2 hash_ALL idx #hash#idx 4 test.t1.v 8 Using join buffer (flat, BNLH join) +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +a +11 +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT a 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 2 Using where +1 SIMPLE t2 ref idx idx 4 test.t1.v 2 +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +a +11 +SET SESSION join_cache_level = DEFAULT; +SET SESSION join_buffer_size = DEFAULT; +DROP TABLE t1,t2; +# +# Bug #802860: crash on join cache + derived + duplicate_weedout +# +SET SESSION optimizer_switch= +'semijoin=on,materialization=off,firstmatch=off,loosescan=off,derived_with_keys=on'; +CREATE TABLE t1 (a int) ; +INSERT IGNORE INTO t1 VALUES (0), (1), (0); +CREATE TABLE t2 (a int) ; +INSERT IGNORE INTO t2 VALUES (0), (3), (0), (2); +SET SESSION join_cache_level = 0; +EXPLAIN +SELECT * FROM (SELECT DISTINCT * FROM t1) t +WHERE t.a IN (SELECT t2.a FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 End temporary +2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary +SELECT * FROM (SELECT DISTINCT * FROM t1) t +WHERE t.a IN (SELECT t2.a FROM t2); +a +0 +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT * FROM (SELECT DISTINCT * FROM t1) t +WHERE t.a IN (SELECT t2.a FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL key0 NULL NULL NULL 3 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary +SELECT * FROM (SELECT DISTINCT * FROM t1) t +WHERE t.a IN (SELECT t2.a FROM t2); +a +0 +SET SESSION join_cache_level = DEFAULT; +DROP TABLE t1, t2; +set @@optimizer_switch=@save_optimizer_switch; |