MySQL 不等号AND検索のインデックス

Question

下記のようなテーブルのインデックスはどう作成するのが最も速いでしょうか。
startとendの範囲からhogeを取得するのが目的で800万レコードほどです。

innodb

+----------+----------+--------+
| start    | end      | hoge   |
+----------+----------+--------+
| 10000    | 11000    | test   |
| 11001    | 13000    | sample |
| 15000    | 16000    | dummy  |
+----------+----------+--------+

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| start    | decimal(10,0) | NO   |     | NULL    |       |
| end      | decimal(10,0) | NO   |     | NULL    |       |
| hoge     | varchar(128)  | NO   |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

SELECT * FROM sample WHERE end >= 12345 AND start <= 12345

# => sample

EXPLAINを使い、いろいろ試したのですが、
だいたい以下のようなスコアでした。

インデックスなし・・・10s
startにPRIMARY・・・5s
start、end複合INDEX・・・5s
endにINDEX・・・2s

2s以上あがらないのはスペックや設定の問題でしょうか。
（innodbバッファプールなどは初期設定だったかと思います。

アドバイスいただけますでしょうか。
よろしくお願いいたします。

Answer 1

以下2通りを試してみてください。

end、startという順番の複合インデックス

startにPRIMARY・・・5s

endにINDEX・・・2s

という結果からendの方がカーディナリティが高いようなので、そちらを先にスキャンさせてみる。

startとendそれぞれに単一カラムインデックス

インデックスマージが効くかもしれないので。
https://dev.mysql.com/doc/refman/5.6/ja/index-merge-optimization.html

---

インデックスの追加だけでは望むパフォーマンスが得られない場合、スペックや設定の見直しに加えてテーブルのパーティション化も検討してみてください。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning.html

Answer 2

実行に2s必要になるのは抽出行数が多いためではないでしょうか？
コストの殆どをメモリの確保やデータ構築、転送に使っているのでは？と推測します。
次のSQLなら早いのではないかと思います。

SELECT count(*) FROM sample WHERE end >= 12345 AND start <= 12345

とか

SELECT * FROM sample WHERE end <= 1 AND start <= 0

INDEX(ソート済み）で何が早いかというと、未ソートなら800万行1つ1つに対してend<=12345を(つまり800万回)チェックしないといけないのに対して、ソート済みなら境界値を探して、それ以下を全て取得すればいいからです。

確認処理 × 800万回 + 抽出処理 × 抽出行数 VS 確認処理 × 最大21回 + 抽出処理 × 抽出行数
※実際は未ソートでもソートしてから境界値を探すほうが早いのでそちらが実施されると思います。

INDEXが片方にしかない場合（複合INDEXでも同じ）、有るほうの抽出は早いでしょうが、無い側の速度は一つ目の条件で絞り込まれた行数に依存します。
また仮に両方に有っても、一つ目の条件で抽出されたグループに対して二つ目のINDEXは使えないか、使えても効果が薄くなると予想できます。
恐らくstartとendに個別にINDEXを作成するのが最速だと思います(オプティマイザが抽出行数が少ないほうを最初に実行するように選んでくれると思います）が、2sより劇的に早くなるかはやってみないとわかりません。