Erhoehte I/O-Last durch Query

Hallo,

ich habe von meinem Webhoster eine Mail bekommen, dass eine MySQL-
Abfrage in meiner PHP-Seite eine zu hohe Datenbanklast verursacht. Ich
habe nun versucht das ganze zu optimieren indem ich Indizes erstellt
habe und Veraenderungen am Query vorgenommen habe.

Anzahl der Datensaetze in den Tabellen:
FirmenKeys: 6000
Firmen: 800
Firm2Kateg: 900
Kategorien: 150

Der Query sieht jetzt folgendermaßen aus:

SELECT DISTINCT
FName,F.Anker,Kat.Name,Kat.ID FROM CL_FirmenKeys AS K
JOIN CL_Firmen AS F ON F.ID=3DK.Firmen_ID
JOIN CL_Firm2Kateg AS F2K ON F.ID=3DF2K.Firm_ID
JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=3DKat.ID
WHERE ( K.Suchwort =3D 'test' OR F.Infos LIKE '%test%'
OR F.Name LIKE '%test%') ORDER BY Kat.Name,F.Name;

Indizes existieren fuer: Suchwort, Firm_ID, Kateg_ID,
Kategorien.Name, Firmen.Name

Ich weiß, dass die Suche mit Wildcards lange dauert, aber ich muss die
Felder so danach durchsuchen.
Gibt es eventuell noch weitere Umformungen die geringere Schreib-/
Lesezugriffe zur Folge haben?

Ich habe in der Dokumentation von Umformungen gelesen, kann diese aber
nicht richtig auf dieses Problem anwenden. Daher meine Frage hier.
Ich hoffe mir kann jemand dabei weiterhelfen, da ich mir keinen
Webspace auf einem eigenen Server leisten kann!

Vielen Dank schon mal für die Anregungen und die Hilfe!

MfG, Ralf...

Re: Erhoehte I/O-Last durch Query

Das "DISTINCT" bitte einfach wegdenken aus dem Query!

Danke...

Re: Erhoehte I/O-Last durch Query

RalfWeb1@gmx.de schrieb:

Wer? Realnamen erhöhen hier ganz ungemein die Chancen auf Lektüre und
Antworten.

>
> Anzahl der Datensaetze in den Tabellen:
> FirmenKeys: 6000
> Firmen: 800
> Firm2Kateg: 900
> Kategorien: 150
>
> Der Query sieht jetzt folgendermaßen aus:
>
> SELECT DISTINCT
> F.Name,F.Anker,Kat.Name,Kat.ID FROM CL_FirmenKeys AS K
> JOIN CL_Firmen AS F ON F.ID=K.Firmen_ID
> JOIN CL_Firm2Kateg AS F2K ON F.ID=F2K.Firm_ID
> JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=Kat.ID
> WHERE ( K.Suchwort = 'test' OR F.Infos LIKE '%test%'
> OR F.Name LIKE '%test%') ORDER BY Kat.Name,F.Name;
>
> Indizes existieren fuer: Suchwort, Firm_ID, Kateg_ID,
> Kategorien.Name, Firmen.Name
>

Aber nicht für Infos? F.ID? Kat.ID? Übrigens wäre hier statt Prosa die
Ausgabe von SHOW CREATE hilfreich gewesen - da muss man weniger
nachdenken, was denn nun Kateg_ID und Firm_ID im Unterschied zu Kat.ID,
F.ID etc. sein mögen.

> Ich weiß, dass die Suche mit Wildcards lange dauert, aber ich muss die
> Felder so danach durchsuchen.


Dann kannste Dir die Indizes auf diese Felder auch sparen. Oder
Volltext-Indizes benutzen.

> Gibt es eventuell noch weitere Umformungen die geringere Schreib-/
> Lesezugriffe zur Folge haben?
>
> Ich habe in der Dokumentation von Umformungen gelesen, kann diese aber

Aber nicht von EXPLAIN? Das ist doch immer das Mittel der Wahl, wenn
irgendwas zu langsam ist ...

Also bitte: EXPLAIN in mysql mit \G ausführen, Ausgabe posten.

In einer stillen Minute könntest Du auch überlegen, ob es *wirklich*
sinnvoll ist, mit einem Begriff über drei Spalten zu suchen. Zumal Du
einerseits erwartest, dass das "Suchwort" exakt übereinstimmt,
andererseits aber "Infos" das Gesuchte nur irgendwo enthalten muss. M.E.
wissen viele Leute ziemlich genau, ob sie einen Namen oder ein Stichwort
suchen. Da kann man ihnen dann auch getrennte Eingabefelder für
anbieten, und gut is. Amazon ist m.E. inzwischen ein wunderbares
Beispiel dafür, warum die Defaultsuche über alle Felder eine Schlechte
Idee ist.

Re: Erhoehte I/O-Last durch Query

Hallo Christian,

danke fuer die schnelle Antwort! Denke dir bitte noch ein "er" an
meinen Email-Namen...

> Aber nicht für Infos? F.ID? Kat.ID? Übrigens wäre hier statt Prosa=
die
> Ausgabe von SHOW CREATE hilfreich gewesen - da muss man weniger
> nachdenken, was denn nun Kateg_ID und Firm_ID im Unterschied zu Kat.ID,
> F.ID etc. sein mögen.

Die Primaerschluessel meiner Tabellen sind alle mit "ID" bezeichnet.
Als Fremdschluessel in anderen Tabellen schreibe ich dann vor das ID
einen Teil des Tabellen-Namens.

> > Ich weiß, dass die Suche mit Wildcards lange dauert, aber ich muss die
> > Felder so danach durchsuchen.
>
> Dann kannste Dir die Indizes auf diese Felder auch sparen. Oder
> Volltext-Indizes benutzen.

Ja, ich habe keinen Index fuer F.Infos erstellt aus diesem Grund.

> Aber nicht von EXPLAIN? Das ist doch immer das Mittel der Wahl, wenn
> irgendwas zu langsam ist ...
>
> Also bitte: EXPLAIN in mysql mit \G ausführen, Ausgabe posten.

"\G" ? Ist das eine Option zur Verwendung in der Kommandozeile? Ich
habe keinen Telnet-Zugang zum Webspace.
Habe EXPLAIN in phpMyAdmin ausgefuehrt.

table type possible_keys key key_len ref rows
Extra
K ALL firmkeys_idx NULL NULL NULL 5623 Using temporary; Using
filesort
F eq_ref PRIMARY,ID PRIMARY 2 K.Firmen_ID 1 Using where
F2K ref f2k_firm_id_idx,f2k_kateg_id_idx f2k_firm_id_idx 2 F.ID
1
Kat eq_ref PRIMARY,ID PRIMARY 2 F2K.Kateg_ID 1

> In einer stillen Minute könntest Du auch überlegen, ob es *wirklich*
> sinnvoll ist, mit einem Begriff über drei Spalten zu suchen. Zumal Du
> einerseits erwartest, dass das "Suchwort" exakt übereinstimmt,
> andererseits aber "Infos" das Gesuchte nur irgendwo enthalten muss. M.E.
> wissen viele Leute ziemlich genau, ob sie einen Namen oder ein Stichwort
> suchen. Da kann man ihnen dann auch getrennte Eingabefelder für
> anbieten, und gut is. Amazon ist m.E. inzwischen ein wunderbares
> Beispiel dafür, warum die Defaultsuche über alle Felder eine Schlechte
> Idee ist.

Urspruenglich hatte ich auch fuer das Suchwort eine LIKE Bedingung mit
Wildcards.
Das habe ich aber schon geaendert um die Performance zu erhoehen. In
Infos steht jedoch mehr drin und auch der Firmenname ist laenger.
Das Suchwort soll auch als Teil des Namens gesucht werden koennen.

MfG, Ralf...

Re: Erhoehte I/O-Last durch Query

Die ID-Felder der Tabellen sind auch indiziert.

Ich kann mir aus dem Ergebnis von EXPLAIN nichts nehmen oder ableiten.
Kann mir bitte jemand genaueres dazu sagen?

MfG...

Re: Erhoehte I/O-Last durch Query

RalfWeb1@gmx.de schrieb:
> Hallo Christian,
>
> danke fuer die schnelle Antwort! Denke dir bitte noch ein "er" an
> meinen Email-Namen...
>

Wie gesagt: Realnamen erhöhen die Chance, gelesen zu werden. "Denk Dir
irgendwas dazu" ist kein Realname (nicht in meiner Welt, jedenfalls).

>> Aber nicht für Infos? F.ID? Kat.ID? Übrigens wäre hier statt Prosa die
>> Ausgabe von SHOW CREATE hilfreich gewesen - da muss man weniger
>> nachdenken, was denn nun Kateg_ID und Firm_ID im Unterschied zu Kat.ID,
>> F.ID etc. sein mögen.
>
> Die Primaerschluessel meiner Tabellen sind alle mit "ID" bezeichnet.
> Als Fremdschluessel in anderen Tabellen schreibe ich dann vor das ID
> einen Teil des Tabellen-Namens.

Und "SHOW CREATE TABLE" funktioniert bei Dir nicht? Oder warum verrätst
Du dessen Ausgabe nicht.

> "\G" ? Ist das eine Option zur Verwendung in der Kommandozeile? Ich
> habe keinen Telnet-Zugang zum Webspace.

Bei den kleinen Datenmengen könntest Du das Ganze ohne großen Aufwand
mal lokal installieren und laufen lassen.

> Habe EXPLAIN in phpMyAdmin ausgefuehrt.
>
> table type possible_keys key key_len ref rows
> Extra
> K ALL firmkeys_idx NULL NULL NULL 5623 Using temporary; Using
> filesort
> F eq_ref PRIMARY,ID PRIMARY 2 K.Firmen_ID 1 Using where
> F2K ref f2k_firm_id_idx,f2k_kateg_id_idx f2k_firm_id_idx 2 F.ID
> 1
> Kat eq_ref PRIMARY,ID PRIMARY 2 F2K.Kateg_ID 1
>

Um eine solche schlecht lesbare Ausgabe zu vermeiden, soll man mysql mit
\G benutzen.

Was Du jedenfalls dieser Ausgabe entnehmen kannst: Für Tabelle K wird
der Index firmkeys_idx nicht benutzt. Statt dessen verwendet MySQL eine
temporäre Datei und sortiert diese. Die Dokumentation verrät Dir
vermutlich, warum das so ist. Sie erklärt Dir auch die Ausgabe von
EXPLAIN. Du findest sie bei dev.mysql.com.


> Das Suchwort soll auch als Teil des Namens gesucht werden koennen.

Wie gesagt: Wenn ich eine Firma suche, dann weiß ich doch, dass es eine
Firma ist. Wenn ich ein Stichwort suche, dann weiß ich, dass es ein
Stichwort ist (und eben *nicht* Teil des Firmennamens). Ich halte es für
ungeschickt, nach Suchbegriffen in beliebigen Feldern zu suchen, denn
die wenigsten Suchanfragen sind so unspezifisch.

Re: Erhoehte I/O-Last durch Query

Hallo Christian,

> Wie gesagt: Realnamen erhöhen die Chance, gelesen zu werden. "Denk Dir
> irgendwas dazu" ist kein Realname (nicht in meiner Welt, jedenfalls).

Google-Groups laesst meines Wissens leider keine Einstellungen machen,
welcher Name angegeben wird.
Standardmaeßig wird die Email-Adresse angezeigt.

> Und "SHOW CREATE TABLE" funktioniert bei Dir nicht? Oder warum verrätst
> Du dessen Ausgabe nicht.

Ist die Struktur der Tabellen jetzt von Bedeutung? Moechte jetzt
eigentlich nicht die
ganzen Strukturen hier posten.

> Bei den kleinen Datenmengen könntest Du das Ganze ohne großen Aufwand
> mal lokal installieren und laufen lassen.

Das waere durchaus moeglich, ist aber meiner Meinung nach nicht
notwendig.


> Was Du jedenfalls dieser Ausgabe entnehmen kannst: Für Tabelle K wird
> der Index firmkeys_idx nicht benutzt. Statt dessen verwendet MySQL eine
> temporäre Datei und sortiert diese. Die Dokumentation verrät Dir
> vermutlich, warum das so ist. Sie erklärt Dir auch die Ausgabe von
> EXPLAIN. Du findest sie bei dev.mysql.com.

Okay, danke. Ich habe mir die dazugehoerigen Kapitel angesehen.
Als einzigen Grund die Indizes nicht zu verwenden habe ich folgendes
gefunden:
"Wenn die Benutzung des Indexes erfordern würde, dass MySQL auf mehr
als 30% der Zeilen in der Tabelle zugreift."
Eventuell ist dies der Fall, kann ich nicht richtig nachvollziehen.

Ich habe mir den Query jetzt noch einmal angeschaut und brauche Hilfe
bei der Umsetzung einer Idee.
Bisher mache ich einen JOIN mit der gesamten FirmenKeys-Tabelle und
diese ist sehr groß.
Im Prinzip muss ich aber nur die Datensaetze JOINEN welche der
Bedingung genuegen.
Das wuerde denke ich eine enorme Einsparung von Zugriffen zur Folge
haben.

Das Problem, ich weiß nicht wie ich das in SQL-Syntax ueberfuehre.

Gibt es sowas in der Art wie das hier?

SELECT Feld FROM Tabelle JOIN (SELECT Feld FROM Tabelle WHERE
Bedingung) AS Alias ON ...

Weil dann koennte ich zuerst nach der Bedingung suchen und viel
weniger DS muessten gejoint werden.

Uebrigens der folgende Query verwendet den Index von Suchwort in
FirmenKeys:

SELECT Firmen_ID FROM FirmenKeys WHERE Suchwort LIKE 'test%'

Diesen muesste ich also irgendwie separat JOINEN.
Das habe ich ausprobiert, aber das scheint nicht so zu
funktionieren(?)

> Wie gesagt: Wenn ich eine Firma suche, dann weiß ich doch, dass es eine
> Firma ist. Wenn ich ein Stichwort suche, dann weiß ich, dass es ein
> Stichwort ist (und eben *nicht* Teil des Firmennamens). Ich halte es für
> ungeschickt, nach Suchbegriffen in beliebigen Feldern zu suchen, denn
> die wenigsten Suchanfragen sind so unspezifisch.

Ich moechte aber auf der Webseite nicht mehrere Sucheingabe-Felder
anbieten,
sondern nur eines schon wegen der Uebersichtlichkeit. Ich verstehe,
theoretisch
waere es moeglich ueber Radiobuttons auszuwaehlen was fuer eine Art
Suchwort
das ist, aber ich glaube das macht sich nicht so gut im Design. Ich
werde dennoch
mal darueber nachdenken...

MfG, Ralf...

Re: Erhoehte I/O-Last durch Query

> SELECT Firmen_ID FROM FirmenKeys WHERE Suchwort LIKE 'test%'
>
> Diesen muesste ich also irgendwie separat JOINEN.

Notfalls koennte ich diese IDs in PHP in ein Array einlesen und dann
in einen weiteren
Query einbauen, der mir dann die restlichen Felder liefert die ich
benoetige.

Ich denke so werde ich es nun machen, außer einer von euch hat noch
eine
zuendende Idee, wie man das in einen Query packen kann ohne große I/O-
Last?

MfG...

Re: Erhoehte I/O-Last durch Query

RalfWeb1@gmx.de schrieb:
> Hallo Christian,
>
>> Wie gesagt: Realnamen erhöhen die Chance, gelesen zu werden. "Denk Dir
>> irgendwas dazu" ist kein Realname (nicht in meiner Welt, jedenfalls).
>
> Google-Groups laesst meines Wissens leider keine Einstellungen machen,
> welcher Name angegeben wird.
> Standardmaeßig wird die Email-Adresse angezeigt.
>

Das ist einzig und allein Dein Problem. Es *gibt* normale Newsreader,
niemand muss Googlegroups zum Posten benutzen.

>> Und "SHOW CREATE TABLE" funktioniert bei Dir nicht? Oder warum verrätst
>> Du dessen Ausgabe nicht.
>
> Ist die Struktur der Tabellen jetzt von Bedeutung? Moechte jetzt
> eigentlich nicht die
> ganzen Strukturen hier posten.
>

Die Definition der Indizes ist von Bedeutung.

>> Bei den kleinen Datenmengen könntest Du das Ganze ohne großen Aufwand
>> mal lokal installieren und laufen lassen.
>
> Das waere durchaus moeglich, ist aber meiner Meinung nach nicht
> notwendig.
>

Du möchtest also Hilfe. Aber Du möchtest denen, die Dir helfen sollen,
nicht die Arbeit erleichtern. Du möchtest Dir noch nichtmal die
Möglichkeit geben, unter voller Kontrolle der Umgebung mit Deiner
Anwendung zu experimentieren. Warum eigentlich nicht? Was spräche
dagegen, einfach mal selber auszuprobieren, wodurch viel I/O entsteht?


> Ich habe mir den Query jetzt noch einmal angeschaut und brauche Hilfe
> bei der Umsetzung einer Idee.
> Bisher mache ich einen JOIN mit der gesamten FirmenKeys-Tabelle und
> diese ist sehr groß.

Du hattest geschrieben:

FirmenKeys: 6000

Das ist alles andere als "sehr groß".


> Im Prinzip muss ich aber nur die Datensaetze JOINEN welche der
> Bedingung genuegen.

Das sollte MySQL eigentlich schon selber machen. Was sagt denn das
Handbuch dazu?

> Das wuerde denke ich eine enorme Einsparung von Zugriffen zur Folge
> haben.
>

Sagt nicht das Explain gerade, dass der Engpass nicht die JOINs oder das
WHERE sind, sondern die Sortierung? Und die findet AFAIK erst nach den
beiden anderen Operationen statt.

Re: Erhoehte I/O-Last durch Query

On 12 Mrz., 21:36, RalfW...@gmx.de wrote:

> Ich habe mir den Query jetzt noch einmal angeschaut und brauche Hilfe
> bei der Umsetzung einer Idee.
> Bisher mache ich einen JOIN mit der gesamten FirmenKeys-Tabelle und
> diese ist sehr groß.

Na ja, sie ist nicht winzig. Sehr gross ist was anderes.

> Im Prinzip muss ich aber nur die Datensaetze JOINEN welche der
> Bedingung genuegen.
> Das wuerde denke ich eine enorme Einsparung von Zugriffen zur Folge
> haben.

Das geht nicht. Dein SELECT hat ein OR, es kann durchaus sein das alle
Datensätze benötigt werden.

Ansonsten ist es sogar besser, die Bedingung auf K ganz wegzulassen.
Dass ändert aber natürlich ggfs. das Abfrageergebnis. Dann hast Du
aber vermutlich Indexnutzung.

Es ist nämlich genau umgekehrt, die Bedingung erfordert
Tabellenzugriffe, die es ohne Index gar nicht geben würde.

Gruß,
Joachim

Re: Erhoehte I/O-Last durch Query

On 13 Mrz., 09:54, joachim.zo...@outcome-ub.de wrote:
> Es ist nämlich genau umgekehrt, die Bedingung erfordert
> Tabellenzugriffe, die es ohne Index gar nicht geben würde.

Ooops, sorry, Gefasel, sollte heissen:

.. die Bedingung erfordert Tabellenzugriffe, während ohne diese
indexiert zugegriffen würde.

Gruß,
Joachim

Re: Erhoehte I/O-Last durch Query

On 12 Mrz., 18:15, RalfW...@gmx.de wrote:
> SELECT DISTINCT
> F.Name,F.Anker,Kat.Name,Kat.ID FROM CL_FirmenKeys AS K
> JOIN CL_Firmen AS F ON F.ID=3DK.Firmen_ID
> JOIN CL_Firm2Kateg AS F2K ON F.ID=3DF2K.Firm_ID
> JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=3DKat.ID
> WHERE ( K.Suchwort =3D 'test' OR F.Infos LIKE '%test%'
> OR F.Name LIKE '%test%') ORDER BY Kat.Name,F.Name;
>
> Indizes existieren fuer: Suchwort, Firm_ID, Kateg_ID,
> Kategorien.Name, Firmen.Name

Auch wenns vermutlich keinen mehr interessiert, das Standarverfahren,
OR in UNION umzuschreiben könnte helfen:

(SELECT
FName,F.Anker,Kat.Name,Kat.ID
FROM CL_FirmenKeys AS K
JOIN CL_Firmen AS F ON F.ID=3DK.Firmen_ID
JOIN CL_Firm2Kateg AS F2K ON F.ID=3DF2K.Firm_ID
JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=3DKat.ID
WHERE K.Suchwort =3D 'test' )
UNION
(SELECT
FName,F.Anker,Kat.Name,Kat.ID
FROM CL_Firmen AS F
JOIN CL_Firm2Kateg AS F2K ON F.ID=3DF2K.Firm_ID
JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=3DKat.ID
WHERE F.Infos LIKE '%test%')
UNION
(SELECT
FName,F.Anker,Kat.Name,Kat.ID
FROM CL_Firmen AS F
JOIN CL_Firm2Kateg AS F2K ON F.ID=3DF2K.Firm_ID
JOIN CL_Kategorien AS Kat ON F2K.Kateg_ID=3DKat.ID
WHERE F.Name LIKE '%test%' )
ORDER BY 3 ,1;

Gruß,
Joachim