EVOLUTION-MANAGER

Edit File: join_cache.test

--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
--enable_warnings

set @save_optimizer_switch=@@optimizer_switch;
set @@optimizer_switch='optimize_join_buffer_size=on';
set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='outer_join_with_cache=on';
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';

set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch;
set names utf8;

CREATE DATABASE world;

use world;

--source include/world_schema1.inc

--disable_query_log
--disable_result_log
--disable_warnings
--source include/world.inc
--enable_warnings
--enable_result_log
--enable_query_log

SELECT COUNT(*) FROM Country;
SELECT COUNT(*) FROM City;
SELECT COUNT(*) FROM CountryLanguage;

show variables like 'join_buffer_size';

set join_cache_level=1;

show variables like 'join_cache_level';

EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
  FROM City,Country,CountryLanguage
  WHERE City.Country=Country.Code AND
        CountryLanguage.Country=Country.Code AND
        City.Name LIKE 'L%' AND Country.Population > 3000000 AND
        CountryLanguage.Percentage > 50 AND
        LENGTH(Language) < LENGTH(City.Name) - 2;

SELECT City.Name, Country.Name, CountryLanguage.Language
  FROM City,Country,CountryLanguage
  WHERE City.Country=Country.Code AND
        CountryLanguage.Country=Country.Code AND
        City.Name LIKE 'L%' AND Country.Population > 3000000 AND
        CountryLanguage.Percentage > 50 AND
        LENGTH(Language) < LENGTH(City.Name) - 2;

set join_cache_level=2;
show variables like 'join_cache_level';

EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

set join_cache_level=3;
show variables like 'join_cache_level';

EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

set join_cache_level=4;
show variables like 'join_cache_level';

EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

SELECT City.Name, Country.Name FROM City,Country
  WHERE City.Country=Country.Code AND 
        Country.Name LIKE 'L%' AND City.Population > 100000;

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;

SELECT Country.Name, Country.Population, City.Name, City.Population
  FROM Country LEFT JOIN City 
       ON City.Country=Country.Code AND
          (City.Population > 5000000 OR City.Name LIKE 'Za%')
  WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;

CREATE INDEX City_Population ON City(Population);
CREATE INDEX City_Name ON City(Name);

--disable_result_log
ANALYZE TABLE City;
--enable_result_log

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;

EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
  FROM Country LEFT JOIN City 
       ON City.Country=Country.Code AND
          (City.Population > 5000000 OR City.Name LIKE 'Za%')
  WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;

DROP INDEX City_Population ON City;
DROP INDEX City_Name ON City;

set join_cache_level=1;

set join_buffer_size=256;
show variables like 'join_buffer_size';