Performance-Problem bei Website-Suche

[constantin]

Hallo zusammen,

meine (selbst zusammengeschraubte) Website-Suche kann Und-Abfragen (z.B. constantin dorit), Nicht-Abfragen (constantin -dorit) und Oder-Abfragen (constantin or dorit). Die Und-Suche funktioniert ganz normal, die nicht-suche ist schon um einiges langsamer und bei der oder-suche steht der Server still und liefert auch nach Minuten kein Ergebnis.

Die Suche involviert drei Tabellen:
w_pages (Tabelle mit den indizierten Seite meiner site)
w_keyword (Suchbegriffe)
w_occ ("Vorkommen"-Tabelle)

w_pages und w_keyword haben als PK eine ID und stehen in einer n:n Beziehung zueinander. Beide sind über ihren PK mit der Vorkommen-Tabelle verknüpft. Die Vorkommen-Tabelle nutzt die beiden Fremdschlüssel ihrerseits als zusammengesetzten PK.

Die Tabellenstruktur und meine Abfragensyntax stimmen, so viel ist sicher. Denn wenn ich die Datensatzanzahl bei der Keyword-Tabelle und bei der Vorkommen-Tabelle zu Testzwecken radikal verkleinere, dann funktioniert auch die Oder-Suche.

Hier die Syntax meiner Oder-Suche:

  
SELECT  
  title,description,url,SUM(v.score) hscore  
FROM  
  w_pages s,w_keyword t,w_occ v, (SELECT s.id FROM  
                                    w_pages s,w_keyword t,w_occ v  
                                  WHERE  
                                    t.keyword LIKE '%constantin%' AND  
                                    v.word_id = t.keyword_id AND  
                                    s.id = v.page_id) a0  
WHERE  
  v.word_id = t.keyword_id AND  
  s.id = v.page_id AND  
  t.keyword like '%dorit%'  OR s.id = a0.id AND  
  s.typ = 'g' AND  
  s.lang = 'd'  
GROUP BY  
  url  
ORDER BY  
  hscore DESC, url ASC  
Limit 0, 100  

An der Server-Konfiguration kann ich nichts ändern, da ich nur ein einfaches Webhosting-Paket ohne eigenen Server habe. Andererseits bin ich sicherlich kein Experte für die Feinheiten bei MySql und vermute, dass sich bei meiner Suche Einiges optimieren lässt.

Könnt Ihr mir Tipps geben, was ich verbessern kann, bzw. wo ich ansetzen sollte?

Vielen Dank,
Constantin

[Cheatah]

Hi,

Könnt Ihr mir Tipps geben, was ich verbessern kann, bzw. wo ich ansetzen sollte?

eliminiere die "LIKE '%...%'" und mache sowas niemals im Leben wieder. Beschäftige Dich mit MATCH AGAINST.

Cheatah

[constantin]

eliminiere die "LIKE '%...%'" und mache sowas niemals im Leben wieder. Beschäftige Dich mit MATCH AGAINST.

Danke cheatah,

ich habe jetzt mittels
CREATE FULLTEXT INDEX kw_full ON w_keyword (keyword)
eine Volltext-Index auf das Feld keyword gelegt und das LIKE in allen Suchabfragen durch MATCH AGAINST ersetzt.

Ergebnis ist eine (gefühlt) deutliche höhere Geschwindigkeit bei der UND-Abfrage und bei der NOT-Abfrage. Bei der Oder-Abfrage steigt er mir aber immer noch aus.

Hier die neue Syntax der Oder-Abfrage

  
SELECT  
  title,description,url,SUM(v.score) hscore  
FROM  
  w_pages s,w_keyword t,w_occ v, (SELECT s.id FROM  
                                    w_pages s,w_keyword t,w_occ v  
                                  WHERE  
                                    MATCH(t.keyword) AGAINST('constantin') AND  
                                    v.word_id = t.keyword_id AND  
                                    s.id = v.page_id) a0  
WHERE  
  v.word_id = t.keyword_id AND  
  s.id = v.page_id AND  
  MATCH(t.keyword) AGAINST('dorit')  OR s.id = a0.id AND  
  s.typ = 'g'  
AND  
  s.lang = 'd'  
GROUP BY  
  url  
ORDER BY  
  hscore DESC, url ASC  
Limit 0, 100  

Habe ich es so richtig gemacht? Lässt sich noch etwas schrauben, meinst Du?

[Cheatah]

Hi,

Ergebnis ist eine (gefühlt) deutliche höhere Geschwindigkeit bei der UND-Abfrage und bei der NOT-Abfrage. Bei der Oder-Abfrage steigt er mir aber immer noch aus.

setze vor das Statement ein "EXPLAIN " und führe es in PhpMyAdmin aus. Wie lautet das Ergebnis?

Cheatah

[constantin]

setze vor das Statement ein "EXPLAIN " und führe es in PhpMyAdmin aus. Wie lautet das Ergebnis?

OK, dann bekomme ich folgende Antwort (sorry wenn das hier jetzt nicht sehr schön formatiert ist):

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 75 Using temporary; Using filesort
1 PRIMARY s ALL PRIMARY NULL NULL NULL 2771
1 PRIMARY t ALL PRIMARY NULL NULL NULL 2967 Using where
1 PRIMARY v index PRIMARY,page_id page_id 12 NULL 239993 Using where; Using index
2 DERIVED s index PRIMARY PRIMARY 4 NULL 2771 Using index
2 DERIVED t fulltext PRIMARY,kw_full kw_full 0    1 Using where
2 DERIVED v eq_ref PRIMARY,page_id PRIMARY 8 p_galleries.s.id,p_galleries.t.keyword_id 1 Using index

[constantin]

So, die Lösung ist gefunden!

Für die Interessierten: Die Syntax der Oder-Abfrage lautet jetzt wie folgt:

  
SELECT  
                title,description,url,SUM(v.score) hscore  
FROM  
                w_pages s,w_keyword t,w_occ v  
WHERE  
                v.word_id = t.keyword_id AND  
                s.id = v.page_id AND  
                s.typ = 'g' AND  
                (MATCH(t.keyword) AGAINST('dorit')  OR s.id IN (SELECT s.id FROM  
                                            w_pages s,w_keyword t,w_occ v  
                                            WHERE  
                                              MATCH(t.keyword) AGAINST('constantin') AND  
                                              v.word_id = t.keyword_id AND  
                                              s.id = v.page_id) )  
GROUP BY  
                url  
ORDER BY  
                hscore DESC, url ASC  
Limit 0, 100  

Was hat sich geändert? 2 Punkte:

1. Die Unterabfrage steht jetzt nicht mehr im FROM-Abschnitt der äußeren Abfrage, sondern im WHERE-Abschnitt. Das hat schon mal ein bischen Performance gebracht.
2. Die beiden Bedingungen, die mit OR verknüpft sind, werden jetzt von einer Klammer umfasst. Und das war tatsächlich entscheidend! Erst damit habe ich den Server dazu gebracht, die Abfrage überhaupt zu bewältigen.

Dementsprechend sieht auch die "EXPLAIN"-Antwort viel besser aus:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s ALL PRIMARY NULL NULL NULL 1386 Using where; Using temporary; Using filesort
1 PRIMARY v ref PRIMARY,page_id,word_id page_id 4 p_galleries.s.id 61 Using index
1 PRIMARY t eq_ref PRIMARY PRIMARY 4 p_galleries.v.word_id 1 Using where
2 DEPENDENT SUBQUERY s eq_ref PRIMARY PRIMARY 4 func 1 Using index
2 DEPENDENT SUBQUERY t fulltext PRIMARY,kw_full kw_full 0    1 Using where
2 DEPENDENT SUBQUERY v eq_ref PRIMARY,page_id,word_id PRIMARY 8 func,p_galleries.t.keyword_id 1 Using where; Using index

Dennoch: Falls jemand Verbesserungs-Tipps hat, wäre ich nach wie vor dankbar:o)

Grüße,
Constantin

[Kalle_B]

Hallo zusammen,

... die nicht-suche ist schon um einiges langsamer und bei der oder-suche steht der Server still und liefert auch nach Minuten kein Ergebnis.

Auf welchen Feldern hast du einen Key?

Kalle

[constantin]

Auf welchen Feldern hast du einen Key?

Hi Kalle,

ich habe folgende Primary Keys definiert:
tabelle,Feld
w_keyword, keyword_id
w_occ, page_id+word_id (zusammengesetzter Schlüssel)
w_pages,id

Dazu muss ich sagen, dass ich diese Sachen komplett in phpmyadmin gemacht habe.

Gruß,
Constantin