Indizierung Fulltext

[double-x]

Hallo,

ich habe ein Problem mit meiner Mysql DB und bin was eine aufwendigere Nutzung betrifft Neuling.

Ich habe eine Tabelle mit 4,4 Millionen URLS.
Nach diesen muss ich suchen können!. Also ich das erste mal nach einer URL suchte hat das 25 Sekunden gedauert.
Dann habe ich das Feld URLs indiziert.
BSP:"SELECT url FROM url WHERE url = www.entwickler-forum.de/newthread.php"
Das dauert dann nach Indizierung 0.01 sek. Super sich war absolut begeistert.
Nun aber wollte ich danach suchten:
"SELECT url, name FROM url WHERE url LIKE '%entwickler-forum%';
Dauer der anfrage ca. 20 sek.

Jetzt habe ich mich im Internet belesen und festgestellt das man nach eine FULLTEXT Indizierung machen kann. Das habe ich getan. Nur an der Zeit hat sich nichts getan. Weiterhin ca. 20sek.

phpMyAdmin sagt nun folgendes zur Struktur im Punkt Indizes:
id = Primary btree Unique: ja Kardinalität: 4,4 Millionen
url= index btree Unique: nein Kardinalität: 4,4 Millionen
url= FULLTEXT Unique: nein Kardinalität: 2,2 Millionen

Für mich als Neuling klingt das als würen nur die hälfte der Urls Fulltext indiziert sein, was natürlich die dauer der anfrage erklärt.

Ich habe bereits die Tabelle analysiert und optimiert. Alles i.o.

double-x

[Falk Prüfer]

Hallo,

ein Volltext-Index macht natürlich nur Sinn, wenn du auch eine Volltextsuche, also mit MATCH (col1,col2,...) AGAINST (expr [search_modifier]) verwendest. Bei deiner Suche mit LIKE kommt weiterhin nur der normale Index zum Tragen und der kann wegen %...% nicht genutzt werden! Die DB macht also nichts anderes, als wenn du gar keinen Index angelegt hättest.
Du solltest dir mal mit EXPLAIN den Ausführungspfad deiner Abfrage ansehen.

Gruß Falk

[double-x]

Vielen Dank.

Da hätte ich ja noch lange Probieren können. Ich werde allerdings die die FULLTEXT Suche mit einer Abfrage aus einer anderen Tabelle mal testen. Da bin ich noch nicht dazu gekommen.

Ich bin auch deinem anderen Tipp mit EXPLAIN mal nachgegangen. Dabei habe verschiedene SELECTs mits vorangestelltem EXPLAIN gemacht. So richtig habe ich da aber auch nichts erfahren. Was sollte ich dann damit erkennen können.

Vielen Dank nochmal für deine Hilfe

double-x

[Falk Prüfer]

Hallo,

EXPLAIN zeigt dir den vom Server gewählten Ausführungspfad für das jeweilige Statement an. Darin erkennst du ob und welche Indizes benutzt werden, ob temporäre Tabellen angelegt werden etc. Du siehst mit etwas Übung also relativ schnell wo die "Schwachstellen" deiner Abfrage liegen.

Gruß Falk

[double-x]

Hallo Falk,

nun habe ich einfach mal ein wenig rumgespielt:
Explain SELECT url FROM url where url='x';
gibt folgendes aus:
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------
| 1 | SIMPLE | url | ref | url_2,url | url_2 | 1002 | const | 1 | Using
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------

d.h. für mich das diese suche auf den Key namens url_2 zugreift. bei diesem handelt es sich um einen INDEX auf der Spalte URL

Diese Abfrage dagegen Explain SELECT url, name FROM url WHERE url LIKE '%test%';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------
| 1 | SIMPLE | url | ALL | NULL | NULL | NULL | NULL | 4452608 |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------

gibt folgende aus. Hier wenn ich es richtig verstehe greift kein Index oder?

Gibt es denn überhaupt irgendeine möglichkeit diese abfrage (außer mit anderer Hardware) zu beschleunigen?

Danke

double-x

[Falk Prüfer]

...Hier wenn ich es richtig verstehe greift kein Index oder?

Genau! Bei einer Abfrage LIKE '%test%' kann MySQL einen etwa vorhandenen Index nicht nutzen. Die Nutzung des Index im Zshg. mit LIKE geht nur, wenn es kein führendes % gibt. Also bei LIKE 'test%'.

...Gibt es denn überhaupt irgendeine möglichkeit diese abfrage (außer mit anderer Hardware) zu beschleunigen?

Also wenn du darauf angewiesen bist den Suchstring auch innerhalb des Textes zu finden und sich die Ergebnismenge nicht schon anderweitig weiter einschräncken läßt, dann hilft wohl nur die Volltextsuche.

Gruß Falk