diff options
Diffstat (limited to 'mysql-test/main/subselect_mat_cost.test')
| -rw-r--r-- | mysql-test/main/subselect_mat_cost.test | 31 |
1 files changed, 27 insertions, 4 deletions
diff --git a/mysql-test/main/subselect_mat_cost.test b/mysql-test/main/subselect_mat_cost.test index 6b7fa4fee64..60763076c45 100644 --- a/mysql-test/main/subselect_mat_cost.test +++ b/mysql-test/main/subselect_mat_cost.test @@ -73,13 +73,13 @@ set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial -- echo Q1.1m: -- echo MATERIALIZATION: there are too many rows in the outer query -- echo to be looked up in the inner table. -set statement optimizer_cache_hit_ratio=20 for EXPLAIN +EXPLAIN SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND surfacearea > 100000; -set statement optimizer_cache_hit_ratio=20 for SELECT count(*) FROM Country +SELECT count(*) FROM Country WHERE (Code IN (select Country from City where City.Population > 100000) OR Name LIKE 'L%') AND surfacearea > 100000; @@ -113,10 +113,19 @@ SELECT * (select Language from CountryLanguage where Percentage > 50) OR City.name LIKE '%Island%'); +EXPLAIN SELECT * FROM Country, City WHERE City.Country = Country.Code AND - Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND + Country.SurfaceArea < 500 AND Country.SurfaceArea > 10 AND + (City.Name IN + (select Language from CountryLanguage where Percentage > 50) OR + City.name LIKE '%Island%'); + +SELECT * + FROM Country, City + WHERE City.Country = Country.Code AND + Country.SurfaceArea < 500 AND Country.SurfaceArea > 10 AND (City.Name IN (select Language from CountryLanguage where Percentage > 50) OR City.name LIKE '%Island%'); @@ -374,12 +383,26 @@ SELECT City.Name, City.Population FROM City JOIN Country ON City.Country = Country.Code GROUP BY City.Name HAVING City.Name IN (select Name from Country where population < 1000000); +--source include/last_query_cost.inc +EXPLAIN +SELECT straight_join City.Name, City.Population +FROM Country JOIN City ON City.Country = Country.Code +GROUP BY City.Name +HAVING City.Name IN (select Name from Country where population < 1000000); +--source include/last_query_cost.inc + +EXPLAIN SELECT City.Name, City.Population -FROM City JOIN Country ON City.Country = Country.Code +FROM Country LEFT JOIN City ON City.Country = Country.Code GROUP BY City.Name HAVING City.Name IN (select Name from Country where population < 1000000); +--source include/last_query_cost.inc +SELECT City.Name, City.Population +FROM City JOIN Country ON City.Country = Country.Code +GROUP BY City.Name +HAVING City.Name IN (select Name from Country where population < 1000000); -- echo -- echo 5. Subqueries with UNION |