MySQL Indexnutzung

Zur richtigen Nutzung von MySQL-Indices habe ich bereits früher schon gebloggt, aber nicht alle Problemstellungen lassen sich so einfach beantworten. Im konkreten Fall ging es um zwei WHERE-Bedingungen: email LIKE '%@somedomain.de' im Vergleich zu SUBSTRING_INDEX(email, '\@', -1) = 'somedomain.de'. Das Ergebnis hat mich ein wenig überrascht.

Grundsätzlich sollte man Funktionen in WHERE-Bedingungen insbesondere bei großen Tabellen möglichst vermeiden, zum Beispiel:

SELECT COUNT(*) FROM tab WHERE (a+1) = 3

Für diese Bedingung muss jede einzelne Zeile gelesen werden, dann wird zum Wert der Spalte "a" eins addiert und vergleichen, ob das Ergebnis 3 ist. Dieses Beispiel würde möglicherweise vom MySQL Query Optimizer erkannt und automatisch optimiert werden, aber das Prinzip bleibt auch für alle komplexeren Funktionen gültig. Zum Vergleich:

SELECT COUNT(*) FROM tab WHERE a = 2

Diese Variante ist inhaltlich identisch, kann aber ganz schnell und einfach über einen Index erledigt werden, sofern die Spalte a indiziert ist.

Im fraglichen Fall ging es einen Schritt weiter um folgende Abfragen:

SELECT COUNT(*) FROM tab WHERE email LIKE '%@somedomain.de'

SELECT COUNT(*) FROM tab WHERE SUBSTRING_INDEX(email, '\@', -1) = 'somedomain.de'

Beide Abfragen können nicht über einen Index gelöst werden, weil die zweite eine Funktion beinhaltet und die erste ein Wildcard (%) am Anfang hat. Die Erklärung dazu liefert die offizielle MySQL-Dokumentation.

Ein EXPLAIN SELECT auf beide Varianten liefert allerdings in doppelter Hinsicht erstaunliches zutage. Erstens ist er für beide Bedingungen absolut gleich und zweitens wird doch ein Index benutzt:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab
         type: index
possible_keys: NULL
          key: email
      key_len: 200
          ref: NULL
         rows: 419461
        Extra: Using where; Using index
1 row in set (0.00 sec)

Als possible_keys wird kein Index genannt, das Query ist also nicht über einen Index abhandelbar. Als key wird dann aber doch der email Index angegeben.

Zum Vergleich ein EXPLAIN SELECT COUNT(*) FROM tab WHERE email LIKE 'something%':

*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: tab
          type: range
 possible_keys: email
           key: email
       key_len: 200
           ref: NULL
          rows: 11
         Extra: Using where; Using index

Zwei Unterschiede fallen sofort auf: Zum einen wird der email-Index bei possible_keys genannt und zum anderen müssen nur noch 11 Zeilen gelesen werden anstatt 419461 im ersten EXPLAIN-Ergebnis.

Warum werden die beiden ersten Queries scheinbar entgegen der MySQL-Dokumentation doch über einen Index bearbeitet? Die Antwort ist relativ einfach: Sie werden es nicht. Eine WHERE-Bedingung mit Funktion kann ebensowenig über einen Index verarbeitet werden, wie ein LIKE mit führendem Wildcard. Bei beiden müssen die Daten aus der Tabelle gelesen und Zeile für Zeile abgeglichen werden.

In diesem Fall wird kein Index benutzt, da bei possible_keys keiner genannt wird, allerdings liest MySQL an Stelle der Datendatei den email-Index als Datenquelle. Dabei wird der Index von Anfang bis Ende durchgelesen und jede Zeile einzeln vergleichen. Sichtbar wird dies auch durch die rows: - Angabe, die den gleichen Wert wie ein SELECT COUNT(*) FROM tab zurückgibt.

Ein Index ist verleichbar mit einer einfachen Tabelle bestehend - in diesem Fall - aus der Spalte email der Haupttabelle und einer Spalte mit dem id der Haupttabelle. Diese quasi Indextabelle zu lesen ist wesentlich schneller, als die Tabellendatei selbst, da letzte über viele zusätzliche Spalten verfügt und dementsprechend wesentlich größer ist (bezogen auf die reine Dateigröße). Durch das Lesen der Indexdatei wird Zeit gespart, einfach weil weniger Daten von der Festplatte geladen werden müssen.

Am Ende unterscheiden sich beide Varianten also nicht in der zu verarbeitenden Datenmenge, sondern lediglich im Vergleich. Während für die SUBSTRING_INDEX zunächst das Feld nach einem @ durchsucht und entsprechend eingekürzt werden muss, kann die LIKE-Variante einige Abkürzungen nehmen. Endet das Feld beispielsweise nicht auf e, kann es nicht mit @somedomain.de enden. Deswegen erwarte ich, dass diese Variante geringfügig schneller sein sollte.