summaryrefslogtreecommitdiff
path: root/mysql-test/main/join_cache.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/join_cache.test')
-rw-r--r--mysql-test/main/join_cache.test65
1 files changed, 61 insertions, 4 deletions
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test
index 4b659345db4..4b6231719c9 100644
--- a/mysql-test/main/join_cache.test
+++ b/mysql-test/main/join_cache.test
@@ -4,6 +4,7 @@ DROP DATABASE IF EXISTS world;
--enable_warnings
--source include/default_optimizer_switch.inc
--source include/default_charset.inc
+--source include/have_innodb.inc
set @org_optimizer_switch=@@optimizer_switch;
set @save_join_cache_level=@@join_cache_level;
@@ -51,16 +52,19 @@ set join_cache_level=1;
show variables like 'join_cache_level';
+--echo # Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -70,6 +74,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -82,16 +87,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=2;
show variables like 'join_cache_level';
+--echo # join_cache_level 2, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 2, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 2, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -101,6 +109,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 2, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -113,16 +122,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=3;
show variables like 'join_cache_level';
+--echo # join_cache_level 3, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 3, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 3, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -132,7 +144,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
-
+--echo # join_cache_level 3, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -146,16 +158,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=4;
show variables like 'join_cache_level';
+--echo # join_cache_level 4, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 4, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 4, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -165,6 +180,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 4, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -175,11 +191,13 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 4, Query 5
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;
+--echo # join_cache_level 4, Query 6
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
@@ -193,17 +211,20 @@ CREATE INDEX City_Name ON City(Name);
ANALYZE TABLE City;
--enable_result_log
+--echo # After Analyze, Query 1
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;
+--echo # After Analyze, Query 2
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;
+--echo # After Analyze, Query 3
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -211,6 +232,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+--echo # After Analyze, Query 4
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
@@ -227,16 +249,19 @@ show variables like 'join_buffer_size';
show variables like 'join_cache_level';
+--echo # join_cache_level 1, Join_buffer_size, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 1, Join_buffer_size, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 1, Join_buffer_size, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -246,6 +271,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 1, Join_buffer_size, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -258,16 +284,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=2;
show variables like 'join_cache_level';
+--echo # join_cache_level 2, Join_buffer_size, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 2, Join_buffer_size, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 2, Join_buffer_size, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -277,6 +306,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 2, Join_buffer_size, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -289,16 +319,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=3;
show variables like 'join_cache_level';
+--echo # join_cache_level 3, Join_buffer_size, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 3, Join_buffer_size, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 3, Join_buffer_size, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -308,6 +341,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 3, Join_buffer_size, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -320,16 +354,19 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=4;
show variables like 'join_cache_level';
+--echo # join_cache_level 4, Join_buffer_size, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 4, Join_buffer_size, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # join_cache_level 4, Join_buffer_size, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -339,6 +376,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # join_cache_level 4, Join_buffer_size, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -372,16 +410,19 @@ show variables like 'join_buffer_size';
set join_cache_level=3;
show variables like 'join_cache_level';
+--echo # Part 2, join_cache_level=3, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Part 2, join_cache_level=3, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Part 2, join_cache_level=3, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -391,6 +432,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # Part 2, join_cache_level=3, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -400,15 +442,18 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # Part 2, join_cache_level=3, Query 5
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--echo # Part 2, join_cache_level=3, Query 6
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--echo # Part 2, join_cache_level=3, Query 7
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -416,6 +461,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
WHERE
Country.Population > 10000000;
+--echo # Part 2, join_cache_level=3, Query 8
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -426,16 +472,19 @@ show variables like 'join_buffer_size';
set join_cache_level=4;
show variables like 'join_cache_level';
+--echo # Part 2, join_cache_level=4, Query 1
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Part 2, join_cache_level=4, Query 2
--sorted_result
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--echo # Part 2, join_cache_level=4, Query 3
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -445,6 +494,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # Part 2, join_cache_level=4, Query 4
--sorted_result
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -454,15 +504,18 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--echo # Part 2, join_cache_level=4, Query 5
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--echo # Part 2, join_cache_level=4, Query 6
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--echo # Part 2, join_cache_level=4, Query 7
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -470,13 +523,14 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
WHERE
Country.Population > 10000000;
+--echo # Part 2, join_cache_level=4, Query 8
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;
-
+--echo # Part 2, join_cache_level=4, Query 9
--replace_column 9 #
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
@@ -484,6 +538,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;
+--echo # Part 2, join_cache_level=4, Query 10
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
@@ -491,6 +546,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
CREATE INDEX City_Name ON City(Name);
+--echo # Part 2, join_cache_level=4, City_Name, Query 1
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -498,6 +554,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+--echo # Part 2, join_cache_level=4, City_Name, Query 2
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
@@ -3175,6 +3232,8 @@ 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');
+INSERT INTO t2 VALUES (100,'Q','q'),(101,'Q','q'),(102,'Q','q'),(103,'Q','q');
+INSERT INTO t2 VALUES (104,'Q','q'),(105,'Q','q'),(106,'Q','q'),(107,'Q','q');
SET SESSION join_cache_level = 1;
@@ -3777,8 +3836,6 @@ drop table t0,t1,t2;
--echo # of LEFT JOIN operations when using join buffer
--echo #
---source include/have_innodb.inc
-
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
col1 varchar(255) NOT NULL DEFAULT '',
View Lorry Controller Status