wieder was zum Thema

[Reiner]

Hi!

Naja, ich bin nicht wirklich der große Datenbank-Optimierungs Experte ;-)

Ich auch noch nicht!

Nur fürs Verständnis, Du hast eine Tabelle mit viel Text in der Spalte "word". Jeder Datensatz verfügt über eine res_id (die nicht unique ist), wozu Du in zwei anderen Tabellen zusätzliche Informationen gespeichert hast. Jetzt hast Du also einen Suchbegriff ("irgendwas"), und willst jetzt diese zusätzlichen Informationen abfragen, die den Datensätzen mit der res_id zugeordent sind, wo der Suchbegriff "irgendwas" irgendwo im langen Text enthalten ist. War das grob richtig?

Mal ganz grob, gibt es eine Tabelle, die Texte enthält, aus der werden Worte in eine andere Tabelle herausgesplittet und einem Rank zugeordnet.

In dem Fall würde ich als erstes mal einen richtigen FULLTEXT Index nehmen, und nicht LIKE (alternativ alle Substrings der Wörter in einer indizierten Tabelel speichern, so ähnlich wie bei der neuen SELF-Suche von Daniela - AFAIK).

EXPLAIN SELECT DISTINCT a,b,c,.....

hm, muss das DISTINCT hier sein?
a,b,c bei GROUP BY?

Stimmt, ist doppelt gemoppelt.

FROM res, res_text,

Du solltest Dir vielleicht besser einen optimalen JOIN-Typ überlegen und angeben

(
SELECT res_words.res_id, res_words.rank
FROM words, res_words
(A)
WHERE word LIKE '%irgendwas%'

Todsünde ;-)
Warum keinen Volltext-Index?

Weil wir ein "pensch"-Feature *) benötigen.
Zudem ist der Volltext bei sehr großen Datenmengen nicht besonders effektiv.

*) "pensch" kommt innerhalb von "Lampenschirm" vor.
Mit einem Volltextindex kann über eine boolsche Suche höchstens "lampe" als Treffer gefunden werden.

Kannst Du auf die Subquery nicht verzichten?

(B)
AND words.id = res_words.word_id
) AS wort
WHERE server_id =1
(C)
AND res.id = res_text.res_id
(D) (woher kommt eigentlich (E)?)

hat vermutlich mit "Using temporary; " zu tun, oder?

AND res.id = wort.res_id
GROUP BY res.id
ORDER BY score DESC , id ASC

Ergebnis ist:

meine Bezeichnung|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra

A|1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|35996|Using temporary; Using filesort

das ist schlecht! Er muss eine nichtmal so kleine temporäre Tabelle erstellen, wenn ich das richtig interpretiere, und kann entsprechend auch keinen Index verwenden.

D|2|DERIVED|words|All|PRIMARY|NULL|NULL|NULL|199878|Using where
auch nicht gut, das kommt vermutlich von LIKE %...%.

Wieso kannst Du nicht einfach einen FULLTEXT-Index auf "word" legen, diese Spalte abfragen, und dann per LEFT JOINs anhand von res_id den zugehörigen Rest auslesen?

Volltext kommt aus obigen Grund nicht in Frage. Zudem ist mindest. eine wichtige Tabelle aus Geschwindigkeitsgründen eine Heap-Tab.
Bei denen funktioniert ein Volltext nicht!

Der Table-Scan am Anfang macht auch nichts, denn die Tabelle ist gegenüber dem Gesamtvolumen der Daten sehr klein.
Ich kann den Table-Scan durch einen kleinen Trick auch noch unterbinden (Stichwort: soundex).

Wenn das nicht geht, würde ich mir überlegen eine HEAP-Tabelle mit passenden Daten als Index zu erstellen.

Genau! ;-)

Gruß
Reiner