「インデックスを使うとなんか早くなるよ」ぐらいの知識しかない自分が
高速化のためにexplainして確認する大切さを身を持って味わう経験ができました。
後の役に立つと思ってメモを残しておきます。

こんなデータがあるとする。

chuumonsテーブル

id	date	kyaku_id
1	2018-01-01 00:00:00	1
2	2018-01-02 00:00:00	1
3	2018-01-01 10:00:00	2

dateは注文発生日、kyaku_idは注文した客のidを表す
dateは秒単位で指定されるので、重複がかなり少ない。

meisaisテーブル

id	chuumon_id	shouhin_id
1	1	1
2	1	2
3	2	1

レコード１つがある注文の商品単位での明細を表す
一度の注文で２種類の商品を買った場合、おなじcuumon_idのレコードが２つできる
shouhin_idは買った商品を表す

やりたいこと
これらのテーブルから以下の条件で注文を抽出したい
・発生時点で商品id=1を３回購入している

これを実現するためにこんなSQLを書いてみた。

SELECT
    chuumons_narrowed_by_item.id
FROM
    chuumons AS chuumons_narrowed_by_item
INNER JOIN
    chuumons AS onaji_kyaku_chuumons
ON
    chuumons_narrowed_by_item.kyaku_id = same_user_chuumons.kyaku_id
    AND
    onaji_kyaku_chuumons.date <= chuumons_narrowed_by_item.date
INNER JOIN
    meisais
ON
    same_user_chuumons.id = meisais.chuumon_id
AND
    meisais.item_id = 1
GROUP BY
    chuumons_narrowed_by_item.id
HAVING
    COUNT(chuumon_narrowed_by_item.id) = 3

注文ごとに同じ客の過去注文をjoinして、item_idごとに集計する。

これで仕様は満たせるのですが
実際にこのクエリを動かすとかなり時間がかかってしまいました。

原因としては、
chuumonsやmeisaisのレコード数がものすごく多い場合、
これらがJOINされまくることによってとてつもないレコード数になってしまう。
そのレコード一つ一つをCOUNTで数え上げることになるので、
計算量も相当なものになってしまう、ということのようです。

というわけで対策としては

shouhin_idよりも絞り込みの効果が大きいカラムで、ベースになるchuumonテーブルを絞り込む
ということが考えられます。
そうすればJOINしてもレコード数はさほど増えないはず。

テーブルの説明でちらっと述べましたが、
chuumonsテーブルには重複が少ないdateというカラムがあります。これを条件に追加してみます。

SELECT
    chuumons_narrowed_by_item.id
FROM
    chuumons AS chuumons_narrowed_by_item
INNER JOIN
    chuumons AS onaji_kyaku_chuumons
ON
    chuumons_narrowed_by_item.kyaku_id = same_user_chuumons.kyaku_id
    AND
    onaji_kyaku_chuumons.date <= chuumons_narrowed_by_item.date
    AND
    chuumons_narrowed_by_item.date BETWEEN '2018-01-01' AND '2018-01-31'
INNER JOIN
    meisais
ON
    same_user_chuumons.id = meisais.chuumon_id
AND
    meisais.item_id = 1
GROUP BY
    chuumons_narrowed_by_item.id
HAVING
    COUNT(chuumon_narrowed_by_item.id) = 3

この改修によって本当に高速化は出来たのですが、すこし気になる現象が起きました。

データ量によって高速化され具合が違う

どの程度効果があったか調べるために、chuumonsのレコード数を変えて試してみました。
（meisaisはchuumonsの約10倍の数があるとする）

chuumonsレコード数	改修前にかかった時間(s)	改修後にかかった時間(s)
20万	10	1
100万	30	27

改修によって速くなってはいるのですが、レコード数が多いとあまり改修の効果が高くないのです
これはなぜなのか、explainして調べてみました

explain結果　レコード数20万の場合（一部省略）

id	select_type	table	type	possible_keys	key	key_len
1	SIMPLE	chuumons_narrowed_by_item	range	PRIMARY, id, date, kyaku_id	date	5
1	SIMPLE	onaji_kyaku_chuumons	ref	PRIMARY, id, date, kyaku_id	kyaku_id	4
1	SIMPLE	meisais	ref	chuumon_id, shouhin_id	chuumon_id	4

explain結果　レコード数100万の場合（一部省略）

id	select_type	table	type	possible_keys	key	key_len
1	SIMPLE	meisais	ref	chuumon_id, shouhin_id	shouhin_id	4
1	SIMPLE	onaji_kyaku_chuumons	eq_ref	PRIMARY, id, kyaku_id	PRIMARY	4
1	SIMPLE	chuumons_narrowed_by_item	ref	PRIMARY, id, date, kyaku_id	kyaku_id	4

結果を出力するにあたって、参照された順番が早いものほど上に表示されます。
なので今回の場合、レコード数20万のときはchuumonsテーブルから、
100万のときはmeisaiテーブルから参照したという違いが見られます。

また、possible_keysとkeysにも違いがあります。
これらはそれぞれ「テーブルの探索に使用可能なキー」「実際に使用したキー」を表しています。
レコード数20万のときは最初にchuumonsテーブルをdateで絞り込んだことがわかります。

つまり、JOINする段階でchuumonsテーブルのレコード数をぐっと小さくすることに成功しているため、
dateの条件を付加した恩恵が大きかったということになります。

なぜこのような違いが現れたのか

MySQLはテーブルを探索する際、どのキーで絞り込むかを
統計情報に基づいて判断しているそうです。

テーブルのレコード数も、どのキーが有効か判断する手がかりとして使われている、ということみたいですね。

今回はMySQLが「テーブル数が100万件あるなら、はじめにこのテーブルをこのキーで絞り込みしよう」と判定した結果、あまり有効でないキーが選択されてしまったようです。

確実に高速化するために

今回の場合、chuumons.dateにインデックスを付加することで
レコード数に関わらず「はじめにchuumonsテーブルをdateで絞り込む」よう動作させることができるようになりました。

インデックスの付いていない日付型のカラムは絞り込みに使用する優先度が低い、ということなのかもしれません。