Indizes werden verwendet, um schnell Datensätze mit bestimmten Spaltenwerten zu finden. Ohne Index müsste MySQL mit der Suche beim ersten Datensatz beginnen und dann die gesamte Tabelle nach passenden Datensätzen durchforsten. Je größer die Tabelle, desto höher sind die Kosten dafür. Wenn die Tabelle einen Index für die fraglichen Spalten aufweist, kann MySQL die Position in der Mitte der Datendatei, an der die Suche beginnen soll, schnell bestimmen, ohne alle Daten in der Datei überprüfen zu müssen. Wenn eine Tabelle 1.000 Datensätze hat, ist dies mindestens hundertmal schneller als ein sequenzielles Lesen. Wenn Sie hingegen auf die meisten Datensätze zugreifen müssen, ist das sequenzielle Lesen schneller, weil die Anzahl der Festplattenzugriffe geringer ist.

Die meisten MySQL-Indizes (PRIMARY KEY, UNIQUE, INDEX und FULLTEXT) sind in B-Trees gespeichert. Zu erwähnende Ausnahmen sind, dass Indizes für raumbezogene Datentypen R-Typen verwenden und MEMORY-Tabellen auch Hash-Indizes unterstützen.

Strings werden automatisch präfixkomprimiert; Gleiches gilt für Leerzeichen am Ende des Strings. Siehe auch Abschnitt 13.1.4, „CREATE INDEX“.

Generell werden Indizes wie nachfolgend beschrieben verwendet. Die spezifischen Eigenschaften von Hash-Indizes, die in Verbindung mit MEMORY-Tabellen verwendet werden, werden am Ende dieses Abschnitts erläutert.

MySQL verwendet Indizes für die folgenden Operationen:

Nehmen wir an, dass Sie folgende SELECT-Anweisung absetzen:

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

Wenn für col1 und col2 ein mehrspaltiger Index vorhanden ist, können die entsprechenden Datensätze direkt abgerufen werden. Sind separate einspaltige Indizes für col1 und col2 vorhanden, dann versucht der Optimierer, den restriktivsten Index zu ermitteln, indem er überprüft, welcher Index weniger Spalten findet; dieser Index wird dann zum Abrufen von Datensätzen benutzt.

Wenn die Tabelle einen mehrspaltigen Index aufweist, kann ein beliebiges linkes Präfix des Indexes vom Optimierer zum Suchen von Datensätzen verwendet werden. Haben Sie beispielsweise einen dreispaltigen Index für (col1, col2, col3), dann bietet dieser Suchfunktionalität für (col1), (col1, col2) und (col1, col2, col3).

MySQL kann keinen Teilindex verwenden, wenn die Spalten kein linkes Präfix für den Index bilden. Betrachten Sie einmal die folgenden SELECT-Anweisungen:

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;

Wenn ein Index für (col1, col2, col3) vorhanden ist, verwenden diese nur die ersten beiden Abfragen. Die dritte und die vierte Abfrage beziehen sich zwar auf indizierte Spalten, aber (col2) und (col2, col3) sind keine linken Präfixe von (col1, col2, col3).

Ein B-Tree-Index kann für Spaltenvergleiche in Ausdrücken benutzt werden, die die Operatoren =, >, >=, <, <= oder BETWEEN verwenden. Der Index kann auch für LIKE-Vergleiche benutzt werden, wenn das Argument zu LIKE ein Konstanten-String ist, der nicht mit einem Jokerzeichen beginnt. Die folgenden SELECT-Anweisungen verwenden beispielsweise Indizes:

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

In der ersten Anweisung werden nur Datensätze mit 'Patrick' <= key_col< 'Patricl' berücksichtigt. In der zweiten Anweisung werden nur Datensätze mit 'Pat' <= key_col< 'Pau' berücksichtigt.

Die folgenden SELECT-Anweisungen verwenden keine Indizes:

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

In der ersten Anweisung beginnt der Wert LIKE mit einem Jokerzeichen. In der zweiten Anweisung ist der LIKE-Wert keine Konstante.

Wenn Sie … LIKE '%string%' verwenden und string mehr als drei Zeichen umfasst, verwendet MySQL den Turbo-Boyer-Moore-Algorithmus zur Initialisierung des Musters für den String; mit diesem Muster wird die Suche dann schneller durchgeführt.

Eine Suche unter Verwendung von col_name IS NULL setzt Indizes ein, wenn col_name indiziert ist.

Ein Index, der nicht alle AND-Ebenen in der WHERE-Klausel einbezieht, wird nicht zur Optimierung der Abfrage benutzt. Um also einen Index verwenden zu können, muss in jeder AND-Gruppe ein Präfix des Indexes benutzt werden.

Die folgenden WHERE-Klauseln verwenden Indizes:

... 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;

Die folgenden WHERE-Klauseln verwenden Indizes nicht:

    /* 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

Manchmal verwendet MySQL auch dann keinen Index, wenn ein solcher vorhanden ist. Ein Umstand, unter dem dies geschieht, liegt vor, wenn der Optimierer der Ansicht ist, dass MySQL bei Verwendung eines Indexes auf einen sehr großen Anteil der Datensätze in der Tabelle zugreifen muss. (In diesem Fall ist ein Tabellenscan mit hoher Wahrscheinlichkeit schneller, weil weniger Suchvorgänge erforderlich sind.) Wenn eine solche Abfrage jedoch dank einer LIMIT-Klausel nur ein paar Datensätze abruft, verwendet MySQL trotzdem einen Index, da sich die Datensätze, die zum Ergebnis gehören, auf diese Weise schneller finden lassen.

Hash-Indizes weisen etwas andere Eigenschaften auf als die soeben beschriebenen: