インデックスは通常迅速に特定のカラム値の行を検索するのに使用されます。インデックスがない場合、関連する行を検索する場合 MySQL は最初の行から始め全テーブルを読まなければなりません。テーブルが大きいほど、コストがかかります。検索しているカラムのインデックスをテーブルが含んでいる場合、MySQL はすべてのデータを検索せずに、データファイルの途中でシークポジションを迅速に決定します。テーブルに 1000 行ある場合、連続して読む場合よりも少なくとも 100 倍の速度で処理が行われます。行のほとんどにアクセスしなければならない場合、連続して読むほうがより高速です。というのも、これはディスクシークの最小化が行われるためです。

ほとんどの MySQL インデックスは (PRIMARY KEY、UNIQUE、INDEX、そして FULLTEXT) は B-trees に保存されています。R-trees を使用する空間データ型インデックスは例外で、MEMORY テーブルでもハッシュインデックスがサポートされています。

文字列の接頭辞とエンドスペースは自動的に圧縮されます。項8.1.13. 「CREATE INDEX 構文」 を参照してください。

一般的に、インデックスは次のように使用されます。ハッシュインデックスの特徴は (MEMORY テーブルで使用される) この節の終わりに説明されています。

MySQL はこれらの演算についてインデックスを使用します。

たとえば次の SELECT ステートメントを発行したとします。

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

col1 と col2 上で複合カラムインデックスが存在する場合、適当な行は直接取得されます。col1 と col2 に別々のシングルカラムインデックスが存在する場合、オプティマイザはインデックス結合最適化 (項4.2.6. 「インデックス結合最適化」を参照) の使用を試みます。または、どのインデックスがより少ない行を検索できるかを決定し、そのインデックスを使用して行を取得することで、もっとも制限力のあるインデックスを検索しようとします。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(col1, col2, col3) に 3 カラムのインデックスがある場合、(col1)、(col1, col2) そして (col1, col2, col3) に対して、インデックスの検索機能を使用できます。

カラムがインデックスの左端の接頭辞を構成していない場合、MySQL では、インデックスを使用できなくなります。次の SELECT ステートメントがあります。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

インデックスが (col1, col2, col3) に存在する場合、最初のクエリーだけがインデックスを使用できます。3 つめと 4 つめのクエリーには、インデックス化したカラムが必要ですが、(col2) と (col2, col3) は (col1, col2, col3) の左端の接頭辞ではありません。

=, >, >=、<, <=あるいは BETWEEN 演算子を使用する表現のカラム比較に、B-tree インデックスが使用可能です。LIKE がワイルドカードキャラクタで始まらない定数文字列の場合、インデックスは LIKE 比較にも使用できます。たとえば、次の SELECT ステートメントはインデックスを使用します。

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

最初のステートメントでは、'Patrick' <= key_col < 'Patricl'を含む行のみ考慮されます。2 つめのステートメントでは、'Pat' <= key_col < 'Pau'を含む行のみ考慮されます。

次の SELECT ステートメントはインデックスを使用しません。

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

最初のステートメントでは、LIKE 値はワイルドカードキャラクタで始まります。2 つめのステートメントでは、LIKE 値は定数ではありません。

もし... LIKE '%文字列%'そして文字列は 3 文字より長い場合、MySQL はTurbo Boyer-Moore アルゴリズムを使用して、文字列のパターンを初期化してから、このパターンを使用して検索をすばやく実行します。

col_name IS NULL を使用した検索では、col_name にインデックスが張られている場合にインデックスが使用されます。

WHERE 節内のすべての AND にかかっていないインデックスは、クエリーの最適化に使用されません。言い換えると、インデックスの使用を可能にするには、インデックスの先頭部分がすべての AND グループで使用されている必要があります。

次の WHERE 節ではインデックスが使用されます。

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

次の WHERE 節ではインデックスが使用されません。

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2
    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10
    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって MySQL がテーブルの大部分のレコードにアクセスする必要が生じるとオプティマイザで推定される場合が挙げられます。(この場合は、必要なシークが減少するため、テーブルスキャンのほうが高速になる可能性が高くなります。) ただしこのクエリーに、レコードの一部のみを取り出す LIMIT が使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。

ハッシュインデックスは先ほど挙げたインデックスとは特徴が異なります。