Wenn MySQL keine Indexe benutzt, obwohl sie existieren

MySQL erlaubt das definieren von Indexen auf Tabellenspalten, um Abfragen insbesondere auf große Tabellen erheblich zu beschleunigen. Nicht immer werden diese Indexe bei Abfragen aber auch genutzt. Was tun, wenn MySQL die Indexe ignoriert, obwohl sie gesetzt sind?

Überprüfen, ob MySQL den angelegten Index überhaupt benutzt

Ob MySQL einen Index überhaupt bei der Berechnung des Ergenisses mit einbezieht, erkennt man normalerweise erst am Performanceeinbruch, wenn sich über die Zeit viele Einträge in der Tabelle gesammelt haben und die Abfragen darauf immer langsamer und langsamer werden. Um MySQL die Information zu entlocken, welche Indexe für eine Abfrage überhaupt herangezogen werden können und welche dann tatsächlich verwendet werden, existiert die EXPLAIN-Funktion. Das Wort EXPLAIN kann jeder SELECT-Abfrage vorangestellt werden. Ausgegeben wird dann nicht das Ergebnis der Abfrage, sondern die Information darüber, wie das Ergebnis zustande kommt.

Ein Beispiel:

CREATE TABLE `demo` (
 `rsid` bigint(20) NOT NULL AUTO_INCREMENT,
 `reference` char(5) NOT NULL,
 `attribute` varchar(64) NOT NULL,
 `attribute_value` varchar(256) NOT NULL,
 PRIMARY KEY (`rsid`),
 KEY `attribute` (`attribute`),
 KEY `referenceid` (`referenceid`)
) ENGINE=MyISAM AUTO_INCREMENT=13280778 DEFAULT CHARSET=utf8

rsid	reference	attribute	attribute_value
1	12345	level of WTF	high
2	12345	onother_key	29738473
3	12346	onother_key	90783737

Nehmen wir an, auf dieser Tabelle existieren 3 Indexe:

• rsid hat einen PRIMARY_KEY (mit AUTO_INCREMENT)
• reference hat einen INDEX
• attribute hat einen INDEX

Ein Beispiel für eine SQL-Abfrage:

SELECT * FROM demo WHERE rsid = 1

In diesem Fall wird ein Filter auf die Spalte reference gelegt und für diese Spalte ist ein Index definiert. Wir können überprüfen, ob dieser Index verwendet wird, indem dem SQL-Befehl ein „EXPLAIN“ vorangestellt wird:

EXPLAIN SELECT * FROM demo WHERE rsid = 1

Ergebnis:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	demo	const	PRIMARY	PRIMARY	8	const	1

In dem Feld „possible_keys“ stehen alle Schlüssel aufgelistet, die für diese Abfrage prinzipiell infrage kommen. In der Spalte „key“ steht hingegen, welcher Index tatsächlich benutzt wurde. Es kann für jeden Filter immer nur ein Index herangezogen werden. Im schlimmsten Fall aber auch gar keiner, was dann einen vollständigen Tabellen-Scan erforderlich macht, der entsprechend lange dauert. In diesem Fall wäre hier das Feld „key“ leer.

Fallstrick: Falscher Datentyp

Nehmen wir folgende SQL-Abfrage:

SELECT * FROM demo WHERE reference = 12345

Diese Abfrage filtert auf eine Datenspalte, für die wir einen Index angelegt haben. Überprüft man mittels EXPLAIN aber, ob der Index auch wirklich verwendet wird, stellt man fest:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	demo	ALL	reference	NULL	NULL	NULL	13280777	Using where

Es ist zwar ein Index da, er wird aber nicht benutzt. Das liegt an dem leicht zu übersehenden Detail, dass bei unserer SQL-Abfrage etwas vergessen wurde: die „Anführungszeichen“. Wir haben nämlich einen Index auf eine CHAR(5) Spalte gelegt, suchen aber in der SQL-Abfrage nach einer Zahl, einem Integer. Damit ist der Index nutzlos, der SQL-Server liefert aber trotzdem das richtige Ergebnis. Formuliert man die SQL-Abfrage richtig:

SELECT * FROM demo WHERE reference = '12345'

… dann lässt sich auch der Index korrekt nutzen:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	demo	ref	reference	reference	15	const	1	Using where