Suchabfrage verbessern

[r. geiseler]

Hallo Falk Prüfer,

ja dass sieht gut aus, Problem ist:

(MATCH (comp.Companyname, ind.d_kat) AGAINST ('Afro'))

, dass geht nicht, denke ich zumindest, habs eben auch Probiert .

Ergebnis:

Incorrect arguments to MATCH

Da ja die Spalte ind.d_kat nicht in der selben Tabelle ist wie die Spalte comp.Companyname, schein dass nicht zu funktionieren. Es müsste ein vereinter Index (Fulltext) über beide Spalten gelegt werden. Deshalb auch die Union. Dies sind quasi zwei einzelne Abfragen, eine prüft ob der Suchbegriff in den Kategorien vorkommt, sucht dann anhand einer Kat_ID in einer Zuordnungstabelle die Firmen_ID`s und dann in der Haupttabelle die entsprechenden Firmen. Die zweite prüft gleichzeitig ob der suchbegriff in der Spalte Companyname vorkommt und muß ja dann nicht den Umweg über die beiden anderen Tabellen nehmen, also ohne Joins.

Und die gefundenen werden dann über union zusammengeführt, also alle die in der kat sind+alle die den begriff im namen haben oder sogar beides.

Vieleicht geht es ja mit deinem ansatz, aber so erstmal nicht. Vieleicht hab ich ja noch nen denkfehler.

Kannst mir ja noch mal einen anstoß geben, wär nett.

Folgende Abfrage funktioniert übrigens fast prima, hatte ich heute festgestellt, leider noch bei manchen suchbegriffen 1-2 sec. teilweise aber auch 0,5sec.

SELECT company.id, company.Companyname, company.STREET, company.ZIPCODE, company.Location
FROM company
INNER JOIN company_industry ON company.id = company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (company.location, company.STREET)AGAINST ('Hamburg') AND (MATCH (company.Companyname)AGAINST ('Abbeizarbeiten'))
UNION
SELECT company.id, company.Companyname, company.STREET, company.ZIPCODE, company.Location
FROM company
INNER JOIN company_industry ON company.id = company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (company.location, company.STREET)AGAINST ('Hamburg')AND (MATCH (industry.d_kat)AGAINST ('Abbeizarbeiten'))ORDER BY Companyname

Alle Indizies werden sauber genutzt, habe ich mit Explain getestet.

Weiß nicht ob hier noch was zu optimieren geht.

mfg
r.geiseler

[Falk Prüfer]

Hallo r.geiseler,

stimmt, dass die beiden Felder nicht aus einer Tabelle kommen, darauf habe ich nicht geachtet.
Aber trotzdem: Bevor wirklich ein Union verwendet wird, würde ich eine ODER-Verknüpfung der Bedingungen bzw. eine Suche IN BOOLEAN MODE ausprobieren.
[highlight=sql]
SELECT ...
FROM company AS comp
INNER JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
INNER JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE MATCH (comp.location) AGAINST ('Hamburg')
AND (
MATCH (comp.Companyname) AGAINST ('Afro') OR
MATCH (ind.d_kat) AGAINST ('Afro')
)
[/highlight]

[highlight=sql]
SELECT ...
FROM company AS comp
INNER JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
INNER JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE MATCH (comp.location) AGAINST ('Hamburg')
AND MATCH (comp.Companyname, ind.d_kat) AGAINST ('Afro' IN BOOLEAN MODE)
[/highlight]

Wenn dann doch ein UNION verwendet wird, dann solltest du überlegen, ob nicht vlt. ein UNION ALL auch ausreicht. Dabei wird auf das implizite DISTINCT verzichtet, es entstehen u.U. aber Dopplungen.

Wenn das alles nichts bringt, dann läßt sich wohl nur noch mit zusätzlicher Redundanz die Abfragegeschwindigkeit verbessern.

Gruß Falk

[r. geiseler]

Hallo Falk,

danke erstmal, habe beide ausprobiert. Funktioniert anscheinend doch mit

AND(MATCH (comp.Companyname, ind.d_kat) AGAINST ('Afro'))

Ja, also beide funktionieren, wobei ich eine Abfrage mit Oder eher nicht nutzen möchte. In allen möglichen Foren steht, "oder" frisst Zeit. Die Zweite Abfrage find ich allerdings nicht schlecht, zumal diese schön überschaubar und nicht so aufgeplustert wirkt. Allerdings habe ich den direkten Vergleich in PHP-MY-Admin durchgeführt und festgestellt, dass der Vorschlag von dir etwa 1,2sec. und dagegen meine oben beschriebene Union 0,45sec. benötigt.

Voraussetzungen waren identisch gleicher Suchbegriff gleicher Ort, wie gesagt manche Suchbegriffe dauern mit meiner Abfrage dann leider länger, dass ist dass was ich nicht ganz verstehe. (erst wenn Order By hinzukommt)

In dem Vergleich habe ich beide Abfrage mit Order By durchgeführt. Ohne Order By ändert sich bei deinem Vorschlag nichts, also ca. 1,2sec. bei der Union wirds dann allerdigs noch etwas schneller, so ca. 0,2sec.

Bei beiden Abfragen habe ich mit Explain geprüft, Indizies werden jeweils genutz, die Anzahl ROWS ist überall 1, dass sollte erstmal alles richtig laufen.

Wie meinst du dass, mit den zusätzlichen Redundanzen?

Vieleicht ist hier ja eine Lösung für dass Problem.

Gruß Ronny

[Falk Prüfer]

Hallo Ronny,

...wobei ich eine Abfrage mit Oder eher nicht nutzen möchte. In allen möglichen Foren steht, "oder" frisst Zeit.

Da haben die Foren recht . Bei einer Oder-Abfrage ist i.a.R. die Indexnutzung sehr eingeschränckt und dadurch die Abfrage langsamer. Wenn die Alternative jedoch ein UNION ist, dann lohnt es aus meiner Sicht schon darüber nachzudenken.

...Allerdings habe ich den direkten Vergleich in PHP-MY-Admin durchgeführt und festgestellt, dass der Vorschlag von dir etwa 1,2sec. und dagegen meine oben beschriebene Union 0,45sec. benötigt.

OK, wenn sich bei Tests herausstellt, dass UNION die schnellere Variante ist, dann ist dagegen ja nichts einzuwenden.

...Wie meinst du dass, mit den zusätzlichen Redundanzen?

Mann könnte z.B. den Text aus ind.d_kat zusätzlich, also redundant, in einem Feld der Tabelle company speichern. Dafür ist natürlich Aufwand zu betreiben, um es konsistent zu halten und im Sinne sauberer Relationen und Normalformen ist es natürlich auch nicht schön. Aber manchmal muß man für Performance eben unpopuläre Entscheidungen treffen

Was ist mit UNION ALL statt UNION?

Gruß Falk

[r. geiseler]

Hallo Falk,

Union All ist kein bisschen schneller und produziert, wie du schon vermutet hast, Doppelte Einträge. Diese müsste ich dann mit einem Distinct wieder ausgrenzen, dann wirds mit sicherheit noch langsamer.

Das Problem ist, die Tabelle Kategorien habe ich ja erst auf Anraten einiger Leute von der Haupttabelle aubgekoppelt. Manche Firmen haben ja mehr als eine Kategorie, dies ließe sich dann nicht in einer Tabelle realisieren. Da müsste ich ja entweder mehrere Spalten hinten an den Firmendatensatz anhängen, ich weiß nicht wie viele Kategorien der jenige möchte. Die Tabelle würde sich aufblähen. Oder die Firma würde pro Zeile eine Kategorie bekommen, dass bedeutet dass diese Firma unter Umständen 5, 8 oder 10 mal vorhanden ist.

Ja also, ich denke ich muß mit der Abfrage erstmal so leben. Viele Suchbegriffe werden ja recht schnell gefunden, bei manchen dauerts halt etwas. Vieleicht liegts einfach auch nur am Server, ist ja auch nicht so das teuerste was so am Markt ist.

Vieleicht hats auch was mit MySql zu tun, kennst du dich da aus?

Gruß
Ronny

[Falk Prüfer]

Hallo Ronny,

...Union All ist kein bisschen schneller und produziert, wie du schon vermutet hast, Doppelte Einträge. Diese müsste ich dann mit einem Distinct wieder ausgrenzen, dann wirds mit sicherheit noch langsamer.

Das normale UNION beinhalted implizit ein DISTINCT.

...Da müsste ich ja entweder mehrere Spalten hinten an den Firmendatensatz anhängen, ich weiß nicht wie viele Kategorien der jenige möchte. Die Tabelle würde sich aufblähen. Oder die Firma würde pro Zeile eine Kategorie bekommen, dass bedeutet dass diese Firma unter Umständen 5, 8 oder 10 mal vorhanden ist.

Naja, da es sich um ein "internes" Suchfeld handeln würde, könnte man das alles (z.B. kommasepariert) in ein Feld schreiben. Aber der dafür notwendige Verwaltungsaufwand steht wohl bei deinen momentanen Antwortzeiten in keinem Verhältnis zum möglichen Nutzen.

...Ja also, ich denke ich muß mit der Abfrage erstmal so leben. Viele Suchbegriffe werden ja recht schnell gefunden, bei manchen dauerts halt etwas.

Was ich noch auf den Prüfstand stellen würde, wäre die Verwendung der Volltextsuche. Da es um solche trivialen Sachen wie Namen und Kategorien geht, wäre da nicht eine Suche LIKE 'Afro%' vorstellbar statt der großen Keule? OK, wenn es eher LIKE '%Afro%' sein muß, dann sind Indizes wieder hinfällig. Da gäbe es dann noch die Möglichkeit mit der Optimierung in der Anwendung bzw. beim Anwender anzufangen. Nach dem Motto: "Name beginnt mit:" => LIKE 'Afro%' - geht sehr fix oder "Suchbegriff muß enthalten sein:" => Volltextsuche - dauert ggf. etwas länger.
Mir ist klar das manche "Entscheider" sowas nicht gerne sehen, aber die eierlegende Wollmilchsau gibt es eben nicht.
Wenn die Abfrage aerodynamisch ausgereizt ist, dann bleibt zur weiteren Steigerung nur mehr Hubraum - sprich ein leistungsfähigerer Server.

...Vieleicht hats auch was mit MySql zu tun, kennst du dich da aus?

Sicher hat das auch was mit der Konfiguration des MySQL-Servers zu tun, aber was Serveroptimierung angeht ist das nicht gerade mein Spezialgebiet

Gruß Falk

[r. geiseler]

Hallo Falk,

Das normale UNION beinhalted implizit ein DISTINCT.

Das bedeutet?

Naja, da es sich um ein "internes" Suchfeld handeln würde, könnte man das alles (z.B. kommasepariert) in ein Feld schreiben. Aber der dafür notwendige Verwaltungsaufwand steht wohl bei deinen momentanen Antwortzeiten in keinem Verhältnis zum möglichen Nutzen.

Da hast du recht, ich hatte es anfangs so, mit Kommatrennung, verursacht aber beim Ausgeben Probleme, also beim trennen, die Kategorien sollen am Schluß mit ausgegeben werden, also Firma X ist eingetragen in Kat. Y,Z,....
Außerdem ist die Seite mehrsprachig, dass heißt beim Eintagen wird ja nur die jeweilige Kategorienummer vergeben und somit kann später anhand der Kat.Nr. jede Kategorie in allen Sprachen welche ich bereits vergeben habe gefunden werden, deshalb auch die gesonderte Tabelle Kat.

Nach dem Motto: "Name beginnt mit:" => LIKE 'Afro%' - geht sehr fix oder "Suchbegriff muß enthalten sein:" => Volltextsuche - dauert ggf. etwas länger.

Kannst du mir da mal eine Beispielabfrage basteln, würde ich dann umgehend probieren, dass wär ja supi wenn dass reicht mit Like. Hatte ich anfangs ja auch mit rumgedoctert, ist mir aber irgendwie nicht so geglückt. Muß dazu sagen, da habe ich mich auch noch nicht so mit den Indexen beschäftigt gehabt, vieleicht wärs ja gegangen. Wenn ich mir so die Foren durchlese sagen viele dass 2.000.000 Datensätze keine größe sind, da verstehe ich garnicht dass hier schon die Reizschwelle für den Arbeitsspeicher erreicht sein soll. Bin extra vor kurzen umgestiegen, hatte vorher 1/4 bei Domaingo und da hatte ich fast die gleichen Zeiten. hmmm.

Gruß Ronny

[Falk Prüfer]

Hallo Ronny,

...Das bedeutet?

UNION ALL mit DISTINCT ist gleichbedeutend mit UNION oder andersherum Wenn man kein DISTINCT benötigt ist UNION ALL schneller als UNION.

...
Kannst du mir da mal eine Beispielabfrage basteln, würde ich dann umgehend probieren, dass wär ja supi wenn dass reicht mit Like.

Naja, halt einfach LIKE statt der Volltextsuche verwenden. Das lößt sicher nicht das UNION oder OR Problem, ist ggfs. aber schneller als die Volltextsuche.

...Wenn ich mir so die Foren durchlese sagen viele dass 2.000.000 Datensätze keine größe sind, da verstehe ich garnicht dass hier schon die Reizschwelle für den Arbeitsspeicher erreicht sein soll. Bin extra vor kurzen umgestiegen, hatte vorher 1/4 bei Domaingo und da hatte ich fast die gleichen Zeiten. hmmm.

Wobei ja Abfragezeiten von 0,2 / 0,45 sec. nicht wirklich langsam sind. Selbst wenn du hier mit weiterer Optimierung noch 30% rauskitzelst, spielt das in einem Bereich der subjektiv kaum bemerkt wird.
Stichwort Domaingo: Das ist Shared Hosting. Hier spielen noch ganz andere - von dir nicht beeinflussbare - Faktoren eine Rolle, die noch dazu größeren Einfluss auf die Insgesamt-Performance haben als eine 50%ige Steigerung der Abfragegeschwindigkeit von 0,4 auf 0,2 sec!
Also wenn du nicht unbedingt den persönlichen Ehrgeiz (oder nichts besseres zu tun) hast, auch noch die letzte Millisekunde rauszukitzeln, dann laß es gut sein .

Gruß Falk

[r. geiseler]

Hallo Falk,

nein hat jetzt nichts mit persl. Ergeiz zu tun. Hatte ja geschrieben, dass einige Suchbegriffe auch 3sec. benötigen.

0,5 oder sogar 1sec. wär auch absolut akzeptabel, zumal dies ja kein Dedicated Server, sondern nur ein V-Server ist. Wie gesagt diese 3sec. sind mein Problem, dass verstehe ich halt nicht, somit ging ich hier in dieses Forum um zu ergründen ob diese Abfrage so i.O. ist. Kann ja auch sein, dass dies anders geht. Ich hatte ja ursprünglich auch eine neue Abfrage, welche ganz am Anfang meines Beitrages steht, benutzen wollen, diese dauerte nur 0,02 sec. (die Abfragezeit beziehe ich immer auf eine direkte Abfragezeit laut PHPMyAdmin).

Diese Abfrage stimmt leider nicht ganz, es werden nur die Ausgegeben, welche den Suchbegriff im Namen und in der Kategorie enthalten. Als du dich dann am Beitrag beteiligt hast, bin ich vom eigendlichen Pfad abgewichen und wir sind bei der vorletzten Abfrage gelandet.

Naja egal, ich bedanke mich auf jeden Fall bei dir, hast dir ja echt Mühe gegeben. Bin eigendlich kein Forenmensch

mfg
Ronny