• Sie befinden sich hier: Startseite > SQL-Backstube > Mehrspaltige LIKE '%suchbegriff%' Abfragen tunen

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

24.09.2010: Mehrspaltige LIKE '%suchbegriff%' Abfragen tunen

Problemstellung

Bei einer flexiblen Suche müssen häufig mehrere Tabellenspalten mit der selben Benutzereingabe durchsucht werden und dann auch noch mit LIKE '%suchbegriff%'. Für gewöhnlich bedeutet dies für die Datenbank einen "Full Table Scan" über die ganze Tabelle. Welche Möglichkeiten gibt es eigentlich, diese Abfrage irgendwie zu beschleunigen?

Getestet mit folgender Datenbank:

* MySQL 5.1.30

Tabellen erzeugen

Listing 1:

1. CREATE TABLE adresse (
2. id INT NOT NULL,
3. vorname VARCHAR(50) NOT NULL,
4. name VARCHAR(50) NOT NULL,
5. email VARCHAR(100) NOT NULL,
6. ort VARCHAR(100) NOT NULL,
7. PRIMARY KEY(id)
8. );

Testdaten einfügen

Listing 2:

1. INSERT INTO adresse VALUES
2. ( 1, 'Vorname-1','Nachname-1','[email protected]', 'Ort-1'),
3. ( 2, 'Vorname-2','Nachname-2','[email protected]', 'Ort-2'),
4. ( 3, 'Vorname-3','Nachname-3','[email protected]', 'Ort-3'),
5. ( 4, 'Vorname-4','Nachname-4','[email protected]', 'Ort-4'),
6. ( 5, 'Vorname-5','Nachname-5','[email protected]', 'Ort-5'),
7. ( 6, 'Vorname-6','Nachname-6','[email protected]', 'Ort-6'),
8. ( 7, 'Vorname-7','Nachname-7','[email protected]', 'Ort-7'),
9. ( 8, 'Vorname-8','Nachname-8','[email protected]', 'Ort-8'),
10. ( 9, 'Vorname-9','Nachname-9','[email protected]', 'Ort-9');

Indices erzeugen

Listing 3:

1. CREATE INDEX sx_adresse_name ON adresse (name);
2. CREATE INDEX sx_adresse_vorname ON adresse (vorname);
3. CREATE INDEX sx_adresse_email ON adresse (email);

Ziel ist es nun, den in Listing 2: rot markierte Datensatz (ID=6) zu finden. Wobei für den Anwender erst einmal unklar ist, in welcher Tabellenspalte der Suchbegriff steht. Im Vorname oder Namen oder vielleicht in der E-Mail.

Die SQL-Abfrage - 1. Versuch

Mit dieser Abfrage werden gleich drei Spalten nach einem bestimmten Eingabewert durchsucht.

Listing 3:

1. SELECT id
2. FROM adresse
3. WHERE vorname LIKE '%-6%'
4. OR name LIKE '%-6%'
5. OR email LIKE '%-6%';
6. +----+
7. | id |
8. +----+
9. | 6 |
10. +----+
11. 1 row in set (0.03 sec)

Listing 4:

1. EXPLAIN
2. SELECT id
3. FROM adresse
4. WHERE vorname LIKE '%-6%'
5. OR name LIKE '%-6%'
6. OR email LIKE '%-6%';
7. +-...-+---------+------+---------------+------+-...-+-------------+
8. | ... | table | type | possible_keys | key | ... | Extra |
9. +-...-+---------+------+---------------+------+-...-+-------------+
10. | ... | adresse | ALL | NULL | NULL | ... | Using where |
11. +-...-+---------+------+---------------+------+-...-+-------------+
12. 1 row in set (0.02 sec)

Funktioniert wie erwartet, aber der Zugriffspfad (Listing 4:) ist leider auch so schlecht wie erwartet. In der EXPLAIN-Spalte type steht ALL (also wird die komplette Tabelle durchsucht).

Query ReWrite und ein kombinierter Index (combined index)

Die bisher angelegten Indices helfen MySQL nicht weiter. Dann entfernen wir diese Indices auch wieder (Listing 5:) und legen einen neuen zusammengesetzten Super-Index an (Listing 6:).

Listing 5:

1. DROP INDEX sx_adresse_name ON adresse;
2. DROP INDEX sx_adresse_vorname ON adresse;
3. DROP INDEX sx_adresse_email ON adresse;

Listing 6:

1. CREATE INDEX sx_adresse_for_like ON adresse (vorname, name, email, id);

Alle(!) Felder der obigen Abfrage (Listing 3:) werden in den Index genommen. Auch die Spalte ID. Wichtig ist dabei die Reihenfolge der Spalten. ID muss ganz nach hinten (also mit der geringsten Bedeutung für den Suchvorgang)

Listing 7:

1. SELECT id
2. FROM adresse
3. WHERE CONCAT(vorname,name,email) LIKE '%-6%';
4. +----+
5. | id |
6. +----+
7. | 6 |
8. +----+
9. 1 row in set (0.02 sec)

Funktioniert wie erhofft. Mit CONCAT werden erst alle drei relevanten Spalten zu einer einzigen verknüpft und diese auf einmal mit LIKE durchsucht. Und wie sieht nun der MySQL Zugriffspfad aus? (Listing 8:)

Listing 8:

1. EXPLAIN
2. SELECT id
3. FROM adresse
4. WHERE CONCAT(vorname,name,email) LIKE '%-6%';
5. +-...-+---------+-------+---------------+---------------------+-...-+--------------------------+
6. | ... | table | type | possible_keys | key | ... | Extra |
7. +-...-+---------+-------+---------------+---------------------+-...-+--------------------------+
8. | ... | adresse | index | NULL | sx_adresse_for_like | ... | Using where; Using index |
9. +-...-+---------+-------+---------------+---------------------+-...-+--------------------------+
10. 1 row in set (0.00 sec)

Perfekt! MySQL nutzt den neuen Index. Interessanterweise mit dem Hinweis possible_key NULL und key dann unser Super-Index sx_adresse_for_like.

zusätzliche Spalten einbeziehen

Soll jetzt aber eine weitere Spalte (ort) angezeigt werden, wird der neue Index nicht mehr genutzt und wir sind wieder beim Full table scan.

Listing 9:

1. EXPLAIN
2. SELECT id, ort
3. FROM adresse
4. WHERE CONCAT(vorname,name,email) LIKE '%-6%';
5. +-...-+---------+------+---------------+-...-+-------------+
6. | ... | table | type | possible_keys | ... | Extra |
7. +-...-+---------+------+---------------+-...-+-------------+
8. | ... | adresse | ALL | NULL | ... | Using where |
9. +-...-+---------+------+---------------+-...-+-------------+
10. 1 row in set (0.00 sec)

Query ReWrite II

Durch einen weiteren Umbau der SQL-Abfrage schaffen wir es aber wieder, MySQL zu überreden, den kombinieren Index doch zu verwenden.

Listing 10:

1. EXPLAIN
2. SELECT a.id, a.ort
3. FROM adresse a
4. JOIN (SELECT id
5. FROM adresse
6. WHERE CONCAT(vorname,name,email) LIKE '%-6%') rs
7. ON a.id = rs.id
8. +----+-------------+------------+--------+---------------+---------------------+-...-+--------------------------+
9. | id | select_type | table | type | possible_keys | key | ... | Extra |
10. +----+-------------+------------+--------+---------------+---------------------+-...-+--------------------------+
11. | 1 | PRIMARY | <derived2> | system | NULL | NULL | ... | |
12. | 1 | PRIMARY | a | const | PRIMARY | PRIMARY | ... | |
13. | 2 | DERIVED | adresse | index | NULL | sx_adresse_for_like | ... | Using where; Using index |
14. +----+-------------+------------+--------+---------------+---------------------+-...-+--------------------------+
15. 3 rows in set (0.02 sec)

Resumee

Für manche SQL-Abfragen können spezielle kombinierte Indices helfen die Abarbeitung zu beschleunigen. Allerdings ist so ein kombinierter Index ziemlich groß und benötigt unter Umständen einiges an Plattenplatz. Und bei INSERT, UPDATE und DELETE-Befehlen eine zeitlich aufwändigere (interne) Aktualisierung des Index.

https://twiedmann.de