結論: MEMORY ストレージエンジンの index_type はデフォルトで HASH なので = or <=>演算子を使用する等価比較にのみ使用される。 >, >=, <, <=, BTWEEN を使うクエリでインデックスを使いたいときは USING BTREE をつけてインデックスを作ること。
MySQL 5.0.75 (やや古めかしい？）を使っていて、 MEMORY ストレージエンジンのテーブルに対して、
SELECT * FROM tbl WHERE col1 = 'val1' AND col2 <= 'val2';
なんてクエリを実行したら妙に遅かった。 col1, col2 の順番で複合インデックスをはっているにもかかわらず。マニュアル読んだらちゃんと書いてありました。あんまり使う機会もないだろうけど（だからこそ？）メモ。
その名前からもわかるように、MEMORY テーブルはメモリ上に格納されます。ハッシュインデックスを使用し、処理が非常に高速で、テンポラリーテーブルを作成するのに大変便利です。
...
The MEMORY ストレージエンジンは HASH と BTREE 両方のインデックスを使って実行します。ここに記すように USING 節 を追加することによりどちらのインデックスであるかを明確にすることが出来ます。
CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;B-Treeインデックスとハッシュインデックスの一般的特徴は 項6.4.5. 「MySQLにおけるインデックスの使用」 に記載されています。
...
MEMORY テーブル上に重複キーをもつハッシュインデックスがある場合(作成されるインデックスは同じ値を持つことが多い)、 キーの値に影響を与えるテーブルアップデートと、全ての消去は非常に処理が遅くなります。処理速度がどの程度落ちるかは、重複の度合いに比例します (或いは、インデックス濃度に反比例します。) 。BTREE インデックス を使用すれば、この問題は生じません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.3 MEMORY ストレージエンジン
=, >, >=、<, <=あるいはBETWEEN演算子を使用する表現のカラム比較に、B-treeインデックスが使用可能です。
...
ハッシュインデックスは先ほど挙げたインデックスとは特徴が異なります。
= or <=>演算子を使用する等価比較にのみ使用されます。(ただし非常に 高速です)<のように値の範囲を検索する比較演算子には使用されません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.1 MySQL のインデックスの使用の仕組み
ということなので、インデックスを定義するときに index_type で BTREE を指定しないと、最初に書いたようなクエリはインデックスが使われず、遅くなる。 BTREE なインデックスがはってあれば早くなる。 EXPLAIN で実行計画をみるとちゃんと type が range になっている。
ということで、 CREATE TABLE のところを修正して、
CREATE TABLE tbl ( ... KEY idx1 USING BTREE (col1, col2), ... ) ENGINE Memory;
という風にした。 ALTER TABLE するなら ALTER TABLE tbl ADD KEY idx1 USING BTREE (col1, col2); とか。すでに同じ名前で HASH インデックスがはってあったら一度 DROP KEY idx1 しないとダメかな。
index_type についてはここに詳しい。 MEMORY ストレージエンジンの場合、 index_type のデフォルトが HASH になってる。
