set @subselect_mat_cost=@@optimizer_switch;
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set long_query_time=0.1;
drop database if exists world;
set names utf8;
create database world;
use world;
CREATE TABLE Country (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
Population int(11) NOT NULL default '0',
Capital int(11) default NULL,
PRIMARY KEY  (Code),
UNIQUE INDEX (Name)
);
CREATE TABLE City (
ID int(11) NOT NULL auto_increment,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY  (ID),
INDEX (Population),
INDEX (Country) 
);
CREATE TABLE CountryLanguage (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0',
PRIMARY KEY  (Country, Language),
INDEX (Percentage)
);
Make the schema and data more diverse by adding more indexes, nullable
columns, and NULL data.
create index SurfaceArea on Country(SurfaceArea);
create index Language on CountryLanguage(Language);
create index CityName on City(Name);
alter table City change population population int(11) null default 0;
select max(id) from City into @max_city_id;
Warnings:
Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
insert into City values (@max_city_id + 1,'Kilifarevo','BGR',NULL);
SELECT COUNT(*) FROM Country;
COUNT(*)
239
SELECT COUNT(*) FROM City;
COUNT(*)
4080
SELECT COUNT(*) FROM CountryLanguage;
COUNT(*)
984
set @@optimizer_switch = 'exists_to_in=on,in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on';

1. Subquery in a disjunctive WHERE clause of the outer query.


Q1.1m:
MATERIALIZATION: there are too many rows in the outer query
to be looked up in the inner table.
EXPLAIN
SELECT Name FROM Country
WHERE (EXISTS (select 1 from City where City.Population > 100000 and
Code = Country) OR
Name LIKE 'L%') AND
surfacearea > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Country	ALL	Name,SurfaceArea	NULL	NULL	NULL	239	Using where
2	MATERIALIZED	City	ALL	Population,Country	NULL	NULL	NULL	4079	Using where
SELECT Name FROM Country
WHERE (EXISTS (select 1 from City where City.Population > 100000 and
Code = Country) OR
Name LIKE 'L%') AND
surfacearea > 1000000;
Name
Algeria
Angola
Argentina
Australia
Bolivia
Brazil
Egypt
South Africa
Ethiopia
Indonesia
India
Iran
Canada
Kazakstan
China
Colombia
Congo, The Democratic Republic of the
Libyan Arab Jamahiriya
Mali
Mauritania
Mexico
Mongolia
Niger
Peru
Saudi Arabia
Sudan
Chad
Russian Federation
United States
drop database world;
set optimizer_switch=@subselect_mat_cost;