[Erledigt] Schnelle Volltextsuche bei 2,5 Mio Einträgen

ratZar · 14.07.2010, 12:31

Hallo zusammen,

hat jemand eine Idee wie ich eine schnelle Volltextsuche bei über 2,5 Millionen Einträgen realisieren kann?
Es geht um eine Ajax Basierte Autocomplete Suche die bislang ~4 Sek. braucht um einen String zu finden, und die Vorschlagsliste aktualisiert.
Etwas beschleunigt habe ich das ganze indem ich erst ab dem 4. eingegebnen Zeichen suchen lasse.

Bislang:
4,3662 Sek.

Code:

SELECT DISTINCT spalte AS spalte FROM tabelle WHERE spalte LIKE '%string%' ORDER BY spalte ASC LIMIT 0,10

Rennt wie Sau:
0,0531 Sek.

Code:

SELECT DISTINCT spalte AS spalte FROM tabelle WHERE MATCH (spalte) AGAINST ('+string' IN BOOLEAN MODE) LIMIT 0,10

Problem dabei:
Die Vorschlagsliste wird bei der zweiten Abfrage erst bei genauen Wort Treffern geladen und ausgegeben. Das hilft dem User dann wenig.

Jetzt meine Frage:
Gibt es eine Möglichkeit die 1. Abfrage soweit zu optimieren das Sie gleichwertige Zeiten erzielt?
Gibt es Möglichkeiten die Datenbank zu optimieren, das die 1. Abfrage besser arbeitet?

Ich sag schonmal Danke.

14.07.2010, 12:57

Ein paar Fragen dazu:

a) Wie sieht denn die Tabellenstruktur von "tabelle" aus. Bsw. was für ein Datentyp ist "spalte".

b) Ist auf "spalte" ein Index?

c) Wie sieht der EXPLAIN Deiner Abfrage aus:

Code:

EXPLAIN SELECT DISTINCT spalte AS spalte FROM tabelle WHERE spalte LIKE '%string%' ORDER BY spalte ASC LIMIT 0,10

Das Ergebnis bitte gut formatiert oder als Bild hier zeigen.

Grüße
Thomas

14.07.2010, 14:29

..oder auch ein allgemeiner Vorschlag dazu. Ich setze mal voraus, dass sich die Texte nicht sehr häufig ändern.

Die bisherige Tabelle:

Code:

CREATE TABLE texte (
 text_id INT NOT NULL,
 datum DATE NOT NULL,
 text VARCHAR(2000),   -- <= diese Spalte wird bisher durchsucht
 ..
 ..
 weitere spalten...
)

Die Spalte "texte" hat zum Beispiel folgenden Inhalt:
"Dies ist ein langer Text .. ist ein langer Text .. ist ein lager Text"

Diesen Text in Worte zerlegen und folgende zusätzliche Tabelle füllen.

Code:

CREATE TABLE worte (
 wort_id NOT NULL,
 text_id NOT NULL,
 wort VARCHAR(20) NOT NULL
)

Jedes Wort wird nur einmal in der Tabelle worte gespeichert

"Dies"
"ist"
"ein"
"langer"
"Texte"

Damit wird die Datenmenge viel kleiner und die Suche geht schneller.

Code:

SELECT text_id 
  FROM worte
  JOIN texte
    ON text_id = text_id
 WHERE wort LIKE '%suche%';

Grüße
Thomas

ratZar · 14.07.2010, 14:48

zu. a) Tabelle: MyISAM - Spalte: varchar(255)
zu. b) Noch kein Index angelegt.
zu c)

ratZar · 14.07.2010, 14:52

Zitat:

Zitat von thomas_w

Damit wird die Datenmenge viel kleiner und die Suche geht schneller.

Code:

SELECT text_id 
  FROM worte
  JOIN texte
    ON text_id = text_id
 WHERE wort LIKE '%suche%';

Grüße
Thomas

Guter Ansatz Thomas, aber es handelt sich bei der zu durchsuchenden Zeile um Maximal 4 bis 5 Wörter.

14.07.2010, 14:59

Wenn kein Index auf spalte existiert, ist dass natürlich einen Versuch wert. Kostet natürlich einigen Speicherplatz. Wenn es nichts bringt, kann der Index jederzeit wieder entfernt werden ( DROP INDEX ... )

Code:

CREATE INDEX sx_tabelle_01 ON tabelle (spalte);

Wie sieht jetzt der EXPLAIN aus..?

Grüße
Thomas

ratZar · 14.07.2010, 15:35

Nach dem Index - >0,00157 Sek. Sprechen für sich.

Jetzt muss ich leider etwas anders an die Sache herangehen, da ca. alle 3min Änderungen in der Tabelle vorgenommen werden und der Index jedes mal neu erstellt werden müsste. Werde eine Tabelle mit der zu durchsuchenden Spalte erstellen, und die Änderungen täglich aus der Haupttabelle via Cronjob aktuallisieren. Dann muss ich den Index täglich nur einmal erstellen.

Danke Thomas

14.07.2010, 15:43

Zitat:

Zitat von ratZar

Nach dem Index - >0,00157 Sek. Sprechen für sich.

Jetzt muss ich leider etwas anders an die Sache herangehen, da ca. alle 3min Änderungen in der Tabelle vorgenommen werden und der Index jedes mal neu erstellt werden müsste. Werde eine Tabelle mit der zu durchsuchenden Spalte erstellen, und die Änderungen täglich aus der Haupttabelle via Cronjob aktuallisieren. Dann muss ich den Index täglich nur einmal erstellen.

MySQL verwaltet den Index selbst, wenn die entsprechende Spalte geändert wird, wird automatisch der Index von MySQL aktualisiert. Da ist kein zusätzliche Aufwand Deinerseits nötig.

Grüße
Thomas

ratZar · 14.07.2010, 16:03

Das stimmt, aber das erstellen dauert zwischen 1 und 2 Minuten. Der Server wäre also permanent damit beschäftigt den Index zu erneuern. Eine Aktualisierung täglich reicht aus.

Danke nochmal für die Hilfe! Bis dann!

Flor1an · 14.07.2010, 23:04

Ich wüsste aber nicht dass du die automatische Aktualisierung deaktivieren kannst in MySQL! Außerdem wird bei einer Änderung ja nicht der komplette Index neu aufgebaut sondern nur das Element was geändert wurde im Index geändert. Es ist also kein wirklich großer Overhead der entsteht.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] Nächste Seite nach 10 Einträgen	JuniorChef	PHP Tipps 2010	3	07.02.2010 17:47
große Mengen an Einträgen löschen	R4v3r	Datenbanken	11	11.11.2009 12:13
Eintragen nur wenn Feld ausgefüllt.	alex9787	PHP Tipps 2009	6	26.10.2009 14:09
ip route eintragen	aha_01	PHP Tipps 2009	3	20.10.2009 14:24
Formulardaten kopieren/mehre datensätze eintragen	22hase	PHP Tipps 2008	2	15.12.2008 13:53
Tipp zur Volltextsuche? InnoDB zu MyISAM?	bicpi	Datenbanken	0	21.09.2006 17:43
Volltextsuche mit InnoDB	guenterfrosch	Datenbanken	2	04.06.2006 14:41
beim eintragen in mysql db gehen 7 von 66 eintrï¿½gen verlor	Marc	PHP Tipps 2006	7	17.05.2006 14:30
Tabelle mit 117.649.000.000/567.869.252.000 Einträgen?		Datenbanken	11	13.03.2006 18:29
volltextsuche in der db mit filterung	Alpha Centauri	PHP-Fortgeschrittene	1	03.07.2005 00:34
Datein in die DB eintragen		PHP Tipps 2005	2	01.06.2005 19:54
Was ist schnelle: Array oder Datenbank?	CSS	PHP Tipps 2005	4	15.03.2005 17:50
Problem mit Bildern, die in eine DB eintragen werden sollen!		PHP Tipps 2005	6	05.03.2005 17:26
[Erledigt] Problem beim Eintragen neuer Daten in die Tabelle		PHP Tipps 2004-2	10	28.12.2004 13:16
[Erledigt] Phrasen - Volltextsuche		Datenbanken	0	25.06.2004 11:13

14.07.2010, 12:31
ratZar Neuer Benutzer Registriert seit: 14.07.2010 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	[Erledigt] Schnelle Volltextsuche bei 2,5 Mio Einträgen Hallo zusammen, hat jemand eine Idee wie ich eine schnelle Volltextsuche bei über 2,5 Millionen Einträgen realisieren kann? Es geht um eine Ajax Basierte Autocomplete Suche die bislang ~4 Sek. braucht um einen String zu finden, und die Vorschlagsliste aktualisiert. Etwas beschleunigt habe ich das ganze indem ich erst ab dem 4. eingegebnen Zeichen suchen lasse. *Bislang:* 4,3662 Sek. Code: SELECT DISTINCT spalte AS spalte FROM tabelle WHERE spalte LIKE '%string%' ORDER BY spalte ASC LIMIT 0,10 *Rennt wie Sau:* 0,0531 Sek. Code: SELECT DISTINCT spalte AS spalte FROM tabelle WHERE MATCH (spalte) AGAINST ('+string' IN BOOLEAN MODE) LIMIT 0,10 Problem dabei: Die Vorschlagsliste wird bei der zweiten Abfrage erst bei genauen Wort Treffern geladen und ausgegeben. Das hilft dem User dann wenig. Jetzt meine Frage: Gibt es eine Möglichkeit die 1. Abfrage soweit zu optimieren das Sie gleichwertige Zeiten erzielt? Gibt es Möglichkeiten die Datenbank zu optimieren, das die 1. Abfrage besser arbeitet? Ich sag schonmal Danke.


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

14.07.2010, 12:57
thomas_w Gast Beiträge: n/a	Ein paar Fragen dazu: a) Wie sieht denn die Tabellenstruktur von "tabelle" aus. Bsw. was für ein Datentyp ist "spalte". b) Ist auf "spalte" ein Index? c) Wie sieht der EXPLAIN Deiner Abfrage aus: Code: EXPLAIN SELECT DISTINCT spalte AS spalte FROM tabelle WHERE spalte LIKE '%string%' ORDER BY spalte ASC LIMIT 0,10 Das Ergebnis bitte gut formatiert oder als Bild hier zeigen. Grüße Thomas

14.07.2010, 14:29
thomas_w Gast Beiträge: n/a	..oder auch ein allgemeiner Vorschlag dazu. Ich setze mal voraus, dass sich die Texte nicht sehr häufig ändern. Die bisherige Tabelle: Code: CREATE TABLE texte ( text_id INT NOT NULL, datum DATE NOT NULL, text VARCHAR(2000), -- <= diese Spalte wird bisher durchsucht .. .. weitere spalten... ) Die Spalte "texte" hat zum Beispiel folgenden Inhalt: "Dies ist ein langer Text .. ist ein langer Text .. ist ein lager Text" Diesen Text in Worte zerlegen und folgende zusätzliche Tabelle füllen. Code: CREATE TABLE worte ( wort_id NOT NULL, text_id NOT NULL, wort VARCHAR(20) NOT NULL ) Jedes Wort wird nur einmal in der Tabelle worte gespeichert "Dies" "ist" "ein" "langer" "Texte" Damit wird die Datenmenge viel kleiner und die Suche geht schneller. Code: SELECT text_id FROM worte JOIN texte ON text_id = text_id WHERE wort LIKE '%suche%'; Grüße Thomas

14.07.2010, 14:48
ratZar Neuer Benutzer Registriert seit: 14.07.2010 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	zu. a) Tabelle: MyISAM - Spalte: varchar(255) zu. b) Noch kein Index angelegt. zu c)

14.07.2010, 14:59
thomas_w Gast Beiträge: n/a	Wenn kein Index auf spalte existiert, ist dass natürlich einen Versuch wert. Kostet natürlich einigen Speicherplatz. Wenn es nichts bringt, kann der Index jederzeit wieder entfernt werden ( DROP INDEX ... ) Code: CREATE INDEX sx_tabelle_01 ON tabelle (spalte); Wie sieht jetzt der EXPLAIN aus..? Grüße Thomas

14.07.2010, 15:35
ratZar Neuer Benutzer Registriert seit: 14.07.2010 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	Nach dem Index - >0,00157 Sek. Sprechen für sich. Jetzt muss ich leider etwas anders an die Sache herangehen, da ca. alle 3min Änderungen in der Tabelle vorgenommen werden und der Index jedes mal neu erstellt werden müsste. Werde eine Tabelle mit der zu durchsuchenden Spalte erstellen, und die Änderungen täglich aus der Haupttabelle via Cronjob aktuallisieren. Dann muss ich den Index täglich nur einmal erstellen. Danke Thomas

14.07.2010, 16:03
ratZar Neuer Benutzer Registriert seit: 14.07.2010 Beiträge: 5 PHP-Kenntnisse: Fortgeschritten	Das stimmt, aber das erstellen dauert zwischen 1 und 2 Minuten. Der Server wäre also permanent damit beschäftigt den Index zu erneuern. Eine Aktualisierung täglich reicht aus. Danke nochmal für die Hilfe! Bis dann!

14.07.2010, 23:04
Flor1an da schreibt der ElePHPant Registriert seit: 18.06.2008 Beiträge: 8.903 PHP-Kenntnisse: Fortgeschritten	Ich wüsste aber nicht dass du die automatische Aktualisierung deaktivieren kannst in MySQL! Außerdem wird bei einer Änderung ja nicht der komplette Index neu aufgebaut sondern nur das Element was geändert wurde im Index geändert. Es ist also kein wirklich großer Overhead der entsteht.