nicht angemeldet
MYSQL - Fulltext und Abfrage über mehrere Tabellen
Hallo!
Ich möchte in einer Datenbank mehrere Tabellen über ein Feld "user_id" verknüpft volltextmäßig durchsuchen lassen. Habe in den Tabellen jeweils Fulltext-Indexe erstellt und in der SELECT-Abfrage JOIN verwendet. Das Problem ist nun, dass MYSQL nicht die Fulltext-Indexe verwendet, sondern nur die Primary-Keys (das sind die über user_id liegenden Indexe, da user_id per auto_increment erzeugt wird), was zu sehr langen Suchzeiten führt. Kann es sein, dass fulltext nicht bei SELECT-Abfragen funzt, oder mache ich was falsch?
Eine Möglichkeit, die ich mir überlegt habe ist, ob es eine Möglichkeit gibt eine verknüpfte Tabelle aus den zu durchsuchenden zu erstellen, die bei Aktualisierung der "Basistabellen" sich autmaotisch mit aktualisert und ich diese so für meine Suche verwenden kann, in dem ich in der verknüpften Tabelle meinen Fulltext-Index erstelle. Ist so was möglich?
Wäre für Ratschläge, auch in andere Richtungen als meine Überlegungen sind, sehr dankbar!
Ach ja: Das ist meine Abfrage: SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id WHERE (m.plz = '64347' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64347%' OR m.plz = '64343' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64343%' OR m.plz = '64344' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64344%' OR m.plz = '64345' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64345%') AND (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
Michael
-
Moin!
Das Problem ist nun, dass MYSQL nicht die Fulltext-Indexe verwendet, sondern nur die Primary-Keys (das sind die über user_id liegenden Indexe, da user_id per auto_increment erzeugt wird), was zu sehr langen Suchzeiten führt.
Kann es sein, dass fulltext nicht bei SELECT-Abfragen funzt, oder mache ich was falsch?
Du solltest dich mal über den Sinn des Volltextindex bei MySQL informieren. Der macht nämlich nicht einfach eine Volltextsuche über deine Datenbank.
Und allein mit einem Volltextindex ist es auch nur in den seltensten Fällen getan. Oftmals muß man seinen Datenbestand für eine effektive Suche selbst eigenhändig indizieren, also einen passenden Parser und Suchschema schreiben, damit man was findet.
Eine Möglichkeit, die ich mir überlegt habe ist, ob es eine Möglichkeit gibt eine verknüpfte Tabelle aus den zu durchsuchenden zu erstellen, die bei Aktualisierung der "Basistabellen" sich autmaotisch mit aktualisert und ich diese so für meine Suche verwenden kann, in dem ich in der verknüpften Tabelle meinen Fulltext-Index erstelle. Ist so was möglich?
Ich schätze, das ist das, was ich einen Absatz weiter oben geschrieben habe.
Wäre für Ratschläge, auch in andere Richtungen als meine Überlegungen sind, sehr dankbar!
EXPLAIN würde zumindest schon mal klären, welche Indices bei dir benutzt werden.
Obwohl: Wenn du mit LIKE '%suchwort%' suchst, dann kommt KEIN Index zum Einsatz, sondern es erfolgt ein Full Table Scan - die Datenbank wird also in Gänze komplett einmal (mit Pech mehrmals) eingelesen und durchforstet.
Und das dauert eben...
Ach ja: Das ist meine Abfrage: SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id WHERE (m.plz = '64347' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64347%' OR m.plz = '64343' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64343%' OR m.plz = '64344' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64344%' OR m.plz = '64345' OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64345%') AND (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
- Sven Rautenberg
--
Die SelfHTML-Developer sagen Dankeschön für aktuell 20065,57 Euro Spendengelder!-
Hallo!
Das Problem ist nun, dass MYSQL nicht die Fulltext-Indexe verwendet, sondern nur die Primary-Keys (das sind die über user_id liegenden Indexe, da user_id per auto_increment erzeugt wird), was zu sehr langen Suchzeiten führt.
Kann es sein, dass fulltext nicht bei SELECT-Abfragen funzt, oder mache ich was falsch?
Du solltest dich mal über den Sinn des Volltextindex bei MySQL informieren. Der macht nämlich nicht einfach eine Volltextsuche über deine Datenbank.
Ich meinte damit, dass die Suche nicht über den jeweiligen Fulltex-Index geschieht.
Und allein mit einem Volltextindex ist es auch nur in den seltensten Fällen getan. Oftmals muß man seinen Datenbestand für eine effektive Suche selbst eigenhändig indizieren, also einen passenden Parser und Suchschema schreiben, damit man was findet.
Eine Möglichkeit, die ich mir überlegt habe ist, ob es eine Möglichkeit gibt eine verknüpfte Tabelle aus den zu durchsuchenden zu erstellen, die bei Aktualisierung der "Basistabellen" sich autmaotisch mit aktualisert und ich diese so für meine Suche verwenden kann, in dem ich in der verknüpften Tabelle meinen Fulltext-Index erstelle. Ist so was möglich?
Ich schätze, das ist das, was ich einen Absatz weiter oben geschrieben habe.
Ist das auch möglich? (Z.B. mit Merge-Tabellen?)
Wäre für Ratschläge, auch in andere Richtungen als meine Überlegungen sind, sehr dankbar!
EXPLAIN würde zumindest schon mal klären, welche Indices bei dir benutzt werden.
Habe ich bereits getestet, es werden nur die Indizes über user_id verwendet.
Obwohl: Wenn du mit LIKE '%suchwort%' suchst, dann kommt KEIN Index zum Einsatz, sondern es erfolgt ein Full Table Scan - die Datenbank wird also in Gänze komplett einmal (mit Pech mehrmals) eingelesen und durchforstet.
Gilt das auch, wenn die Felder, die mit LIKE durchsucht werden, gar nicht indiziert sind? EXPLAIN liefert mir immer die Auskunft, dass die Indizes über user_id verwendet werden, weil die Abfrage ja hierüber verknüpft ist.
Bei dieser Abfrage: EXPLAIN SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage
FROM verzeichnis AS v, member AS m WHERE (
MATCH ( m.firma, m.name, m.vorname, m.strasse )
AGAINST ( '+suchwort*'
IN BOOLEAN
MODE ) OR
MATCH ( v.begriffe )
AGAINST ( '+suchwort*'
IN BOOLEAN
MODE ) )
ORDER BY m.firma
liefert mir EXPLAIN die Info, dass überhaupt kein Index verwendet wird, obwohl ich einen Fulltext-Index in member über firma, name, vorname, strasse sowie einen in verzeichnis über begirffe gelegt habe.Und das dauert eben...
Danke schon mal für die Antwort, habe mich wahrscheinlich ein bisserl zu unverständlich ausgedrückt.
Michael
-
-
Hallo,
Ich möchte in einer Datenbank mehrere Tabellen über ein Feld "user_id" verknüpft volltextmäßig durchsuchen lassen. Habe in den Tabellen jeweils Fulltext-Indexe erstellt und in der SELECT-Abfrage JOIN verwendet. Das Problem ist nun, dass MYSQL nicht die Fulltext-Indexe verwendet,
Woran erkennst Du das?
Ach ja: Das ist meine Abfrage: SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end)
FROM (verzeichnis AS v INNER JOIN member AS m USING (user_id))
^hier und ^hier würden mir als Interpreter Klammern fehlen.LEFT JOIN werbung AS w ON m.user_id=w.user_id
WHERE (m.plz = '64347' OR v.plzen LIKE '%alle%'
[1]
OR v.plzen LIKE '%64347%' OR m.plz = '64343'
[2]
OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64343%'
[3] [4]
OR m.plz = '64344' OR v.plzen LIKE '%alle%'
[5]
OR v.plzen LIKE '%64344%' OR m.plz = '64345'
[6]
OR v.plzen LIKE '%alle%' OR v.plzen LIKE '%64345%')
[7] [8]
AND (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firmaDie mit [pf] gekennzeichneten Stellen sind die performance-fresser ;-)). Bei einem LIKE "%text%" kann der Text irgendwo mitten im Feldinhalt stehen. Das kann kein Index abdecken. Versuche das zu vermeiden. Meistens geht das nur durch Überdenken der Datenbankstruktur. Wieso kann in v.plzen irgendwas vor und irgendwas nach "alle" stehen? Muss das so sein? Wieso kann in v.plzen irgendwas vor und irgendwas nach der PLZ stehen? Muss das so sein?
viele Grüße
Axel
-
Hallo!
Woran erkennst Du das?
Habe das mit EXPLAIN geprüft.
Die mit [pf] gekennzeichneten Stellen sind die performance-fresser ;-)). Bei einem LIKE "%text%" kann der Text irgendwo mitten im Feldinhalt stehen. Das kann kein Index abdecken. Versuche das zu vermeiden. Meistens geht das nur durch Überdenken der Datenbankstruktur. Wieso kann in v.plzen irgendwas vor und irgendwas nach "alle" stehen? Muss das so sein? Wieso kann in v.plzen irgendwas vor und irgendwas nach der PLZ stehen? Muss das so sein?
Danke, dass mit den Klammern ist eine gute Idee, die Suche über plz habe ich nun mal zum Testen weggelassen und folgende Abfrage durchgeführt: EXPLAIN SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM (verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id) WHERE (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
Das Resultat war:
table type possible_keys key key_ len ref rows Extra
v ALL PRIMARY NULL NULL NULL 114187 Using temporary; Using filesort
m eq_ref PRIMARY PRIMARY 4 db657894.v.user_id 1 Using where
w ref user_id user_id 4 m.user_id 22
Wie gesagt, Primary sind nicht die Fulltext-Indexe. Der Fulltext-Index in member liegt über firma, name, vorname, strasse und der in verzeichnis über begriffe, also genau das was ich abfrage. Als ich mal nur in einer Tabelle abgefragt habe, wurde der Fulltext-index auch brav genommen.Michael Spiller
-
Hallo,
EXPLAIN SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM (verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id) WHERE (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
Da Du drei Tabellen joinst, ist natürlich zunächst mal der JOIN zu erstellen. Die EXPLAIN-Resultate beziehen sich offensichtlich hauptsächlich darauf. Der Fulltext-Index wird nur noch für die Volltextsuche wirksam, oder funktioniert die nicht?
Das Resultat war:
table type possible_keys key key_ len ref rows Extra
v ALL PRIMARY NULL NULL NULL 114187 Using temporary; Using filesortDie Tabelle v hat ein Problem, da hier kein Index genutzt werden kann. Gibt es außer PRIMARY welche? Es muss eine temporäre Tabelle erstellt werden und filesort muss angewendet werden, um Group By und Order By unter einen Hut zu bringen, da diese zwei unterschiedliche Felder betreffen. Hier wäre zum Optimieren anzusetzen.
m eq_ref PRIMARY PRIMARY 4 db657894.v.user_id 1 Using where
Das ist doch optimal. Der type eq_ref ist für einen JOIN optimal. Der mögliche Index wird angewendet. WHERE wird angewendet, um die zu joinenden Datensätze einzuschränken. Top.
w ref user_id user_id 4 m.user_id 22
Nicht optimal, aber wahrscheinlich nicht zu ändern. Der type ref resultiert aus dem LEFT OUTER JOIN. Sonst OK.
http://www.mysql.com/doc/en/EXPLAIN.html
viele Grüße
Axel
-
Hallo!
Konnte dank Deiner Hilfe nun das Problem lösen.
Durch die EXPLAIN-Dokumentation auf mysql.com konnte ich nun das EXPLAIN-Resultat richtig deuten. Es hat mich gewundert, dass bei verzeichnis kein Index verwendet wird und habe ein bisschen mit der Abfrage rumgespielt und so die Lösung gefunden!
EXPLAIN SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM (verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id) WHERE (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
Zwischen den beiden MATCH(...) AGAINST(...) habe ich aus dem OR ein AND gemacht - und es funzt nun! Die Abfrage ist in wenigen ms ausgeführt! Mein Glaube an MySQL ist wieder da ;-)!
Ganz herzlichen Dank für Deine Unterstützung Alex - und ein frohes Weihnachtsfest!
Liebe Grüße
Michael-
Sorry, meine natürlich Axel (Tippfehler)
-
Hallo,
EXPLAIN SELECT m.user_id, m.berecht, v.branche1, v.branche2, v.branche3, v.mo_v1, v.mo_v2, v.mo_n1, v.mo_n2, v.di_v1, v.di_v2, v.di_n1, v.di_n2, v.mi_v1, v.mi_v2, v.mi_n1, v.mi_n2, v.do_v1, v.do_v2, v.do_n1, v.do_n2, v.fr_v1, v.fr_v2, v.fr_n1, v.fr_n2, v.sa_v1, v.sa_v2, v.sa_n1, v.sa_n2, v.so_v1, v.so_v2, v.so_n1, v.so_n2, m.firma, m.strasse, m.plz, m.ort, m.tel, m.email, m.homepage, MIN(w.start), MAX(w.end) FROM (verzeichnis AS v INNER JOIN member AS m USING (user_id) LEFT JOIN werbung AS w ON m.user_id=w.user_id) WHERE (MATCH (m.firma,m.name,m.vorname,m.strasse) AGAINST ('+anwalt*' IN BOOLEAN MODE) OR MATCH (v.begriffe) AGAINST ('+anwalt*' IN BOOLEAN MODE)) GROUP BY m.user_id ORDER BY m.firma
Zwischen den beiden MATCH(...) AGAINST(...) habe ich aus dem OR ein AND gemacht - und es funzt nun! Die Abfrage ist in wenigen ms ausgeführt! Mein Glaube an MySQL ist wieder da ;-)!
Das ändert aber den ganzen Sinn der Abfrage entscheidend. Natürlich ist eine AND-Verknüpfung besser für die Nutzung des Index auf v.begriffe, aber es werden auch entsprechend weniger Datensätze der Bedingung entsprechen. Meiner Meinung nach, solltest Du eher einen Index auf v.user_id versuchen. Den Index auf m.user_id gibt's ja schon.
viele Grüße
Axel
-
Hallo!
Das ändert aber den ganzen Sinn der Abfrage entscheidend. Natürlich ist eine AND-Verknüpfung besser für die Nutzung des Index auf v.begriffe, aber es werden auch entsprechend weniger Datensätze der Bedingung entsprechen. Meiner Meinung nach, solltest Du eher einen Index auf v.user_id versuchen. Den Index auf m.user_id gibt's ja schon.
Stimmt, das habe ich eben erst gemerkt, ich habe aber einen Index auf v.user_id (sogar PRIMARY), aber sobald das OR wieder anstatt dem AND da steht, funzt das wieder net, also bei v wird kein Index benutzt. Komisch!
Grüße
Michael -
Hallo Axel!
Das ändert aber den ganzen Sinn der Abfrage entscheidend. Natürlich ist eine AND-Verknüpfung besser für die Nutzung des Index auf v.begriffe, aber es werden auch entsprechend weniger Datensätze der Bedingung entsprechen. Meiner Meinung nach, solltest Du eher einen Index auf v.user_id versuchen. Den Index auf m.user_id gibt's ja schon.
Habe es nun wirklich hinbekommen: Mit UNION zwei Abfragen verbinden, die sich nur im MATCH(...) AGAINST(...)-Teil unterscheiden, ist zwar nicht ganz so elegant, aber funzt und das sogar super schnell!
Nochmals danke, frohes Fest und viele Grüße!
Michael
-
-
-
-
-