AND population > 1000 $current_key= $index_continent_population["Asia"][1000]->next; $result_buffer= []; while ($current_key) { if ($current_key["continent"] != "Asia") break; 14/98
indepyear < 0 AND region = 'Eastern Asia' AND population > 1000000; /* 等価検索しているのは (continent, region)、indepyearとpopulationは範囲検索 $index["Asia"]["Eastern Asia"] (またはその逆)を population > 1000000 で絞るか、indepyear < 0で絞るか ALTER TABLE country ADD INDEX idx_continent_region_population(continent, region, population), ADD INDEX idx_continent_region_indepyear(continent, region, indepyear); */ 22/98
BY name; /* $index["Asia"]->first; が効かせられるように、 WHEREのカラムが先、 $current_key->next が効かせられるようにORDER BYのカラム、の順で狙う ALTER TABLE country ADD INDEX idx_continent_name(continent, name) */ 31/98
region = 'Eastern Asia' ORDER BY indepyear; /* $index[$continent][$region] (または逆) ->first; $current_key->next がindepyearの順になるように ALTER TABLE country ADD INDEX idx_continent_region_indepyear(continent, region, indepyear) */ 34/98
continentで刈り込んでループに入って、ループの中で追加のフィルタリングをかけつつ追加 ソートは発生しないパターン ‐ ALTER TABLE country DROP KEY idx_continent_region_indepyear, ADD KEY idx_continent_indepyear(continent, indepyear); 36/98
BY population LIMIT 5; /* ORDER BY LIMITの早抜けができないように、 `idx_continent_population_indepyear` を消して `id x_continent` だけを足す ALTER TABLE country DROP INDEX idx_continent_population_indepyear, ADD INDEX idx_continent(continent); */ 47/98
GROUP BY region; /* $index["Asia"] の刈り込みはできているけど、regionがソート済みでないのでテンポラリーテーブ ルが発生している ALTER TABLE country ADD INDEX idx_continent_region(continent, region); */ 51/98
ON country.code= countrylanguage.countrycode WHERE country.continent = 'Asia' AND countrylanguage.language = 'English' ORDER BY countrylanguage.percentage LIMIT 5; ↓ 84/98
countrylanguage ON country.code= countrylanguage.countrycode WHERE country.continent = 'Asia' AND countrylanguage.language = 'English' ORDER BY countrylanguage.percentage LIMIT 5; /* 外側をcountrylanguageにしたいので、WHERE, ORDER BYの順で idx_language_percentage 内側がcountryになるのでONとWHEREの idx_code_continent ALTER TABLE countrylanguage ADD INDEX idx_language_percentage(language, percentage); ALTER TABLE country ADD INDEX idx_code_continent(code, continent); */ 85/98
countrylanguage.language, country.population, countrylanguage.percentage FROM country JOIN countrylanguage ON country.code= countrylanguage.countrycode WHERE country.continent = 'Asia' AND countrylanguage.language = 'English' ORDER BY countrylanguage.percentage LIMIT 5; ↓ 86/98
FORCE INDEX(idx_continent) JOIN countrylanguage ON countrylanguage.countrycode = co untry.code WHERE countrylanguage.language = 'Japanese' GROUP BY country.continent; ↓ 91/98