Plan changes from hash_index_merge to index_merge with new optimizer defaults

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 @@use_stat_tables= PREFERABLY;

set @@optimizer_use_condition_selectivity=4;

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=4;

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;

EXPLAIN

SELECT Country.Name, Country.Population, City.Name, City.Population

FROM Country LEFT JOIN City 

ON City.Country=Country.Code AND

(City.Population > 5000000 OR City.Name LIKE 'Za%')

WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where

1	SIMPLE	City	index_merge	City_Population,City_Name	City_Population,City_Name	4,35	NULL	96	Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNL join)