revision-id: b6ff2b590519ab141fca0cd597abf0ffbde0ac14 (mariadb-10.6.1-370-gb6ff2b59051) parent(s): f00c21ce9785f2ebffd7e500773d29dfe7d45768 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-21 18:07:48 +0300 message: Update test results (5) --- mysql-test/include/index_merge_ror_cpk.inc | 1 + mysql-test/main/index_merge_innodb.result | 2 +- mysql-test/main/index_merge_myisam.result | 2 +- mysql-test/main/join_cache.result | 286 ++++++++++++++--------------- mysql-test/main/join_cache.test | 64 ++++++- mysql-test/main/myisam_icp.result | 2 +- mysql-test/suite/maria/icp.result | 2 +- 7 files changed, 210 insertions(+), 149 deletions(-) diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc index c2da93cf383..59c7a6194a3 100644 --- a/mysql-test/include/index_merge_ror_cpk.inc +++ b/mysql-test/include/index_merge_ror_cpk.inc @@ -60,6 +60,7 @@ commit; # Verify that range scan on CPK is ROR # (use index_intersection because it is impossible to check that for index union) +--replace_column 11 FLTRD explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; # CPK scan + 1 ROR range scan is a special case select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result index 0d1e20b6860..4eac7afa1fd 100644 --- a/mysql-test/main/index_merge_innodb.result +++ b/mysql-test/main/index_merge_innodb.result @@ -547,7 +547,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 0.10 Using where +1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 FLTRD Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index f9234230b82..1eec57a9834 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1379,7 +1379,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 0.30 Using index condition; Using where +1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 FLTRD Using index condition; Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 02c87eb8195..3e62b54bf5d 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -58,8 +58,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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; @@ -88,9 +88,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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 @@ -136,8 +136,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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; @@ -166,9 +166,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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 @@ -292,8 +292,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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; @@ -322,9 +322,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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 @@ -410,8 +410,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where -1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 FLTRD 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 @@ -437,8 +437,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where -1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 FLTRD 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 @@ -476,8 +476,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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; @@ -506,9 +506,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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 @@ -554,8 +554,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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; @@ -584,9 +584,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD 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 @@ -632,8 +632,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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; @@ -662,9 +662,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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 @@ -710,8 +710,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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; @@ -740,9 +740,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD 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 @@ -821,8 +821,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -851,9 +851,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -923,8 +923,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD 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') @@ -1021,8 +1021,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -1051,9 +1051,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1095,8 +1095,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -1123,8 +1123,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD 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') @@ -1215,8 +1215,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 52 NULL # 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # 0.74 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 52 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code ROWS FLTRD 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 @@ -1243,8 +1243,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 52 NULL 17 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 52 NULL 17 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD 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 @@ -1280,8 +1280,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1310,9 +1310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1354,8 +1354,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1382,8 +1382,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD 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') @@ -1477,8 +1477,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1507,9 +1507,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1551,8 +1551,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1579,8 +1579,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD 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') @@ -1674,8 +1674,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1704,9 +1704,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1748,8 +1748,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1776,8 +1776,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD 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') @@ -1871,8 +1871,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1901,9 +1901,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1945,8 +1945,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -1973,8 +1973,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD 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') @@ -2072,8 +2072,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -2102,9 +2102,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2146,8 +2146,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -2176,8 +2176,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -2206,9 +2206,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2250,8 +2250,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD 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; @@ -2280,8 +2280,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2310,9 +2310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2354,8 +2354,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2384,8 +2384,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2414,9 +2414,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2458,8 +2458,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2488,8 +2488,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2518,9 +2518,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2562,8 +2562,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2592,8 +2592,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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2622,9 +2622,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2666,8 +2666,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD 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; @@ -2745,8 +2745,8 @@ EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL range Population,Country Population 4 NULL # 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country # 100.00 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City NULL range Population,Country Population 4 NULL ROWS FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country ROWS FLTRD 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 @@ -2899,8 +2899,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -2908,8 +2908,8 @@ 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 partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; set @@join_cache_level=@save_join_cache_level; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 1bea4cfcbf8..4564b63cd5c 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -51,6 +51,7 @@ set join_cache_level=1; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -61,6 +62,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -82,6 +84,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=2; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -92,6 +95,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -148,6 +152,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -158,6 +163,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -195,6 +201,7 @@ CREATE INDEX City_Name ON City(Name); ANALYZE TABLE City; --enable_result_log +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -206,6 +213,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -229,6 +237,7 @@ show variables like 'join_buffer_size'; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -239,6 +248,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -260,6 +270,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=2; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -270,6 +281,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -291,6 +303,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -301,6 +314,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -322,6 +336,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -332,6 +347,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -374,6 +390,7 @@ show variables like 'join_buffer_size'; set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -384,6 +401,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -412,6 +430,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -429,6 +448,7 @@ show variables like 'join_buffer_size'; set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -439,6 +459,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -457,6 +478,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -466,6 +488,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -480,7 +503,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P Country.Population > 10000000; ---replace_column 10 # +--replace_column 10 ROWS 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -494,6 +517,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population CREATE INDEX City_Name ON City(Name); +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -513,6 +537,7 @@ show variables like 'join_buffer_size'; set join_cache_level=5; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -523,6 +548,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -541,6 +567,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -550,6 +577,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -566,6 +594,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=6; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -576,6 +605,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -594,6 +624,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -603,6 +634,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -619,6 +651,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=7; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -629,6 +662,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -647,6 +681,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -656,6 +691,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -672,6 +708,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=8; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -682,6 +719,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -700,6 +738,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -709,6 +748,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -728,6 +768,7 @@ show variables like 'join_buffer_size'; set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -738,6 +779,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -756,6 +798,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -768,6 +811,7 @@ SELECT Name FROM City set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -778,6 +822,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -796,6 +841,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -808,6 +854,7 @@ SELECT Name FROM City set join_cache_level=5; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -818,6 +865,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -836,6 +884,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -848,6 +897,7 @@ SELECT Name FROM City set join_cache_level=6; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -858,6 +908,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -876,6 +927,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -888,6 +940,7 @@ SELECT Name FROM City set join_cache_level=7; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -898,6 +951,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -916,6 +970,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -928,6 +983,7 @@ SELECT Name FROM City set join_cache_level=8; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -938,6 +994,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -956,6 +1013,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -977,7 +1035,7 @@ SELECT City.Name, Country.Name FROM City,Country set join_cache_level=8; set join_buffer_size=384; ---replace_column 10 # +--replace_column 10 ROWS 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; @@ -1041,12 +1099,14 @@ CREATE INDEX City_Name ON City(Name); ANALYZE TABLE City, Country; --enable_result_log +--replace_column 11 FLTRD 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; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index dba40aa6369..211773621c1 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter +1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter DROP TABLE t1; # # diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 7c0b9c701e0..8117293cd80 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -409,7 +409,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL 3 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort DROP TABLE t1; # #