SQL Query optimieren

Hallo,

ich habe eine SQL Abfrage, die mir insgesamt lediglich 236 Zeilen als
ergebnis liefert.

Die Abfrage läuft über 4 Tabellen, die alle mit einem Index über die ID
versehen sind.

Die Abfrage dauert ca. 2 Sekunden ... was ich ziemlich lange finde.
Zumal diese Abfrage in einer Schleife modifiziert ca. 20 Mal ausgeführt
wird - damit wäre ich dann schon bei 40 Sekunden ... das ist zu lange.

Wie gehe ich am besten vor, um die Abfrage zu optimieren?

Danke

Mark






--
www.zeitfuerwahrheit.de

************************************************************ ************
*
Beim großen Manual, ich habe gesprochen! *
*
************************************************************ ************
*
"Ich habe Dinge gesehen, die ihr Menschen niemals glauben würdet. *
Gigantische Schiffe, die brannten, draußen vor der Schulter des Orion. *
Und ich habe C-Beams gesehen, glitzernd im Dunkel, *
nahe dem Thannhäuser-Tor. *
All diese Momente werden verloren sein... in der Zeit, *
so wie ... Tränen im Regen." *
*
************************************************************ ************

Re: SQL Query optimieren

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: SQL Query optimieren

Mark Knochen wrote:

[...]

> Wie gehe ich am besten vor, um die Abfrage zu optimieren?


"Irgendwie fühle ich mich ab und zu nach dem Aufstehen unwohl.
Das beunruhigt mich, ich schlafe doch genug. Herr Doktor, was=20
könnte mir fehlen?"

Als erstes mal bisschen mehr Info geben, die Glaskugeln hier
sind leider beschlagen...

Martin

Re: SQL Query optimieren

Andreas Kretschmer wrote:
> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
> Begriff 'Explain' sagt Dir etwas?
>
OK, ich versuchs mal, die Abfrage lautet:

SELECT
buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_nummer,buchungen.infotext,
buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutto,buchungen.haben_brutto,
buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
WHERE
(haben_brutto = '1000' OR soll_brutto = '1000')
AND realkonto = '1'
AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
AND (buchungen.nebenkosten_id = nebenkosten.ID OR
buchungen.nebenkosten_id = '0')
AND (buchungen.brennmaterial_id = brennmaterial.ID OR
buchungen.brennmaterial_id = '0')
AND buchungen.datum >= '2006-01-01'
AND buchungen.datum <= '2006-12-31'
GROUP BY bu_id ORDER BY buchungen.datum

Über die folgende Tabellen liegt ein Index:

Tabelle buchungen -> Index über bu_id
Tabelle objektverzeichnis -> Index über obj_id
Tabelle nebenkosten -> Index über ID
Tabelle Heizkosten -> Index über ID


Mark

Re: SQL Query optimieren

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: SQL Query optimieren

Mark Knochen schrieb:

> SELECT
> [...]
> WHERE
> (haben_brutto = '1000' OR soll_brutto = '1000')
> AND [...]
>
> Über die folgende Tabellen liegt ein Index:
>
> Tabelle buchungen -> Index über bu_id
> Tabelle objektverzeichnis -> Index über obj_id
> Tabelle nebenkosten -> Index über ID
> Tabelle Heizkosten -> Index über ID

Warum hast Du keinen Index für haben_brutto und soll_brutto? Warum hast
Du keine Indizes für alle anderen Bewertungskriterien? Hier solltest Du
mal als erstes tätig werden.

Gruß. Claus
--
,~°O O
O ,´ / |/|\
/ |¯`. Das neue Hochzeits-Branchenbuch im Internet ,´ / | |\
/__| `~...............................................~´ /___|/ /

Re: SQL Query optimieren

Mark Knochen schrieb:
> Andreas Kretschmer wrote:
>> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
>> Begriff 'Explain' sagt Dir etwas?
>>
> OK, ich versuchs mal, die Abfrage lautet:
>
> SELECT
> buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_nummer,buchungen.infotext,
> buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutto,buchungen.haben_brutto,
> buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
> FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
> WHERE
> (haben_brutto = '1000' OR soll_brutto = '1000')
> AND realkonto = '1'
> AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
> AND (buchungen.nebenkosten_id = nebenkosten.ID OR
> buchungen.nebenkosten_id = '0')
> AND (buchungen.brennmaterial_id = brennmaterial.ID OR
> buchungen.brennmaterial_id = '0')
> AND buchungen.datum >= '2006-01-01'
> AND buchungen.datum <= '2006-12-31'
> GROUP BY bu_id ORDER BY buchungen.datum
>
> Über die folgende Tabellen liegt ein Index:
>
> Tabelle buchungen -> Index über bu_id
> Tabelle objektverzeichnis -> Index über obj_id
> Tabelle nebenkosten -> Index über ID
> Tabelle Heizkosten -> Index über ID
>

Indizes liegen nicht "über Tabellen", sondern auf bestimmten Spalten. In
diesem Fall hast Du lediglich die IDs indiziert. Deine Abfrage verwendet
aber neben den IDs auch andere Felder...

Unabhängig davon gilt natürlich Andreas' jetzt schon ZWEIMAL gegebener
Hinweis auf EXPLAIN.

Re: SQL Query optimieren

Mark Knochen wrote:
> Andreas Kretschmer wrote:
>> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
>> Begriff 'Explain' sagt Dir etwas?
>>
> OK, ich versuchs mal, die Abfrage lautet:
>
> SELECT
> buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_nummer,buchungen.infotext,
> buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutto,buchungen.haben_brutto,
> buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
> FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
> WHERE
> (haben_brutto = '1000' OR soll_brutto = '1000')
> AND realkonto = '1'
> AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
> AND (buchungen.nebenkosten_id = nebenkosten.ID OR
> buchungen.nebenkosten_id = '0')
> AND (buchungen.brennmaterial_id = brennmaterial.ID OR
> buchungen.brennmaterial_id = '0')
> AND buchungen.datum >= '2006-01-01'
> AND buchungen.datum <= '2006-12-31'
> GROUP BY bu_id ORDER BY buchungen.datum

Das sieht wie ein unvollständig normalisiertes Datenmodell aus.
Tabelle `buchungen` referenziert wahlweise Objekte, Nebenkosten
oder Brennmaterial (oder nichts). FK=0 steht anscheinend für
"nichts referenziert", obwohl da NULL besser wäre.

Ich würde das als UNION SELECT schreiben:

SELECT ... FROM buchungen JOIN objektverzeichnis
USING (obj_id)
WHERE ...
UNION
SELECT ... FROM buchungen JOIN nebenkonsten
ON (...)
WHERE ...
UNION
....

Andererseits wird aus `objektverzeichnis`, `nebenkosten` und
`brennmaterial` nie etwas anderes als die JOIN-Spalte referenziert.
Ich frage mich, warum man da überhaupt JOINen muß.


XL

Re: SQL Query optimieren

Ich habe jetzt den Index der Tabelle 'buchungen' auf über alle Spalten
erweitert, die ich ausgeben möchte:

Explain gibt aus:

1, 'SIMPLE', 'brennmaterial', 'index', 'ID', 'ID', '4', '', 11, 'Using
index; Using temporary; Using filesort'

1, 'SIMPLE', 'nebenkosten', 'index', 'ID', 'ID', '4', '', 12, 'Using index'

1, 'SIMPLE', 'objektverzeichnis', 'ALL', 'obj_id', '', '', '', 127, ''

1, 'SIMPLE', 'buchungen', 'ALL', 'index', '', '', '', 6307, 'Using where'

Mark

Re: SQL Query optimieren

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: SQL Query optimieren

Mark Knochen schrieb:
> Andreas Kretschmer wrote:
>
>> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
>> Begriff 'Explain' sagt Dir etwas?
>>
> OK, ich versuchs mal, die Abfrage lautet:
>
> SELECT
> buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_nummer,buchungen.infotext,
> buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutto,buchungen.haben_brutto,
> buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
> FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
> WHERE
> (haben_brutto = '1000' OR soll_brutto = '1000')
> AND realkonto = '1'
> AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
> AND (buchungen.nebenkosten_id = nebenkosten.ID OR
> buchungen.nebenkosten_id = '0')
> AND (buchungen.brennmaterial_id = brennmaterial.ID OR
> buchungen.brennmaterial_id = '0')
> AND buchungen.datum >= '2006-01-01'
> AND buchungen.datum <= '2006-12-31'
> GROUP BY bu_id ORDER BY buchungen.datum
>
> Über die folgende Tabellen liegt ein Index:
>
> Tabelle buchungen -> Index über bu_id
> Tabelle objektverzeichnis -> Index über obj_id
> Tabelle nebenkosten -> Index über ID
> Tabelle Heizkosten -> Index über ID

Mich würde in diesem Fall auch ein Discribe für die Tabellen
interessieren (Vielleicht auch gleich noch eine Angabe über die
Zeilenzahl der Tabellen).

Was mir persönlich nicht gefällt (weil für mich IDs immer
Number suggerieren) sind die Bedingungen
XXX_Id = 'irgendwas'

Ausserdem verhindert ein intensives OR die Verwendung von Indexen.

Und ein Vergleich Datum mit 'ein Datumliteral' läßt mich auch
nicht glücklich blicken.

So far,
Michael

Re: SQL Query optimieren

Hallo,
Frage:was bewirkt in deiner Abfarge die Group by Klausel ??
mfg
Markus Bretscher

Mark Knochen schrieb:
> Andreas Kretschmer wrote:
>
>> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
>> Begriff 'Explain' sagt Dir etwas?
>>
> OK, ich versuchs mal, die Abfrage lautet:
>
> SELECT
> buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_nummer,buchungen.infotext,
> buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutto,buchungen.haben_brutto,
> buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
> FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
> WHERE
> (haben_brutto = '1000' OR soll_brutto = '1000')
> AND realkonto = '1'
> AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
> AND (buchungen.nebenkosten_id = nebenkosten.ID OR
> buchungen.nebenkosten_id = '0')
> AND (buchungen.brennmaterial_id = brennmaterial.ID OR
> buchungen.brennmaterial_id = '0')
> AND buchungen.datum >= '2006-01-01'
> AND buchungen.datum <= '2006-12-31'
> GROUP BY bu_id ORDER BY buchungen.datum
>
> Über die folgende Tabellen liegt ein Index:
>
> Tabelle buchungen -> Index über bu_id
> Tabelle objektverzeichnis -> Index über obj_id
> Tabelle nebenkosten -> Index über ID
> Tabelle Heizkosten -> Index über ID
>
>
> Mark

Re: SQL Query optimieren

In article ,
markus bretscher writes:

> Hallo,
> Frage:was bewirkt in deiner Abfarge die Group by Klausel ??
> mfg
> Markus Bretscher

> Mark Knochen schrieb:
>> Andreas Kretschmer wrote:
>>
>>> Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können=
.. Der
>>> Begriff 'Explain' sagt Dir etwas?
>>>
>> OK, ich versuchs mal, die Abfrage lautet:
>> SELECT
>> buchungen.buchungstext,buchungen.belegnummer,objektverzeichn is.obj_numme=
r,buchungen.infotext,
>> buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchunge n.soll_brutt=
o,buchungen.haben_brutto,
>> buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
>> FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
>> WHERE
>> (haben_brutto =3D '1000' OR soll_brutto =3D '1000')
>> AND realkonto =3D '1'
>> AND (buchungen.obj_id =3D objektverzeichnis.obj_id OR buchungen.obj_id =
=3D '0')
>> AND (buchungen.nebenkosten_id =3D nebenkosten.ID OR
>> buchungen.nebenkosten_id =3D '0')
>> AND (buchungen.brennmaterial_id =3D brennmaterial.ID OR
>> buchungen.brennmaterial_id =3D '0')
>> AND buchungen.datum >=3D '2006-01-01'
>> AND buchungen.datum <=3D '2006-12-31'
>> GROUP BY bu_id ORDER BY buchungen.datum

Ist doch ganz einfach: sie bewirkt, daß die Query ungültiges SQL ist,
das nur von MySQL akzeptiert wird und dann irgendwas auslöst, das den
meisten unklar ist.

Re: SQL Query optimieren

Ach so


Harald Fuchs schrieb:
> In article ,
> markus bretscher writes:
>
>
>>Hallo,
>>Frage:was bewirkt in deiner Abfarge die Group by Klausel ??
>>mfg
>>Markus Bretscher
>
>
>>Mark Knochen schrieb:
>>
>>>Andreas Kretschmer wrote:
>>>
>>>
>>>>Anstatt Deiner Signatur hättest Du ja mal die Abfrage zeigen können. Der
>>>>Begriff 'Explain' sagt Dir etwas?
>>>>
>>>
>>>OK, ich versuchs mal, die Abfrage lautet:
>>>SELECT
>>>buchungen.buchungstext,buchungen.belegnummer,objektverzei chnis.obj_nummer,buchungen.infotext,
>>>buchungen.brennmaterial_id,buchungen.nebenkosten_id,buchu ngen.soll_brutto,buchungen.haben_brutto,
>>>buchungen.betrag_brutto,buchungen.datum,buchungen.obj_id
>>>FROM buchungen, nebenkosten, objektverzeichnis, brennmaterial
>>>WHERE
>>>(haben_brutto = '1000' OR soll_brutto = '1000')
>>>AND realkonto = '1'
>>>AND (buchungen.obj_id = objektverzeichnis.obj_id OR buchungen.obj_id = '0')
>>>AND (buchungen.nebenkosten_id = nebenkosten.ID OR
>>>buchungen.nebenkosten_id = '0')
>>>AND (buchungen.brennmaterial_id = brennmaterial.ID OR
>>>buchungen.brennmaterial_id = '0')
>>>AND buchungen.datum >= '2006-01-01'
>>>AND buchungen.datum <= '2006-12-31'
>>>GROUP BY bu_id ORDER BY buchungen.datum
>
>
> Ist doch ganz einfach: sie bewirkt, daß die Query ungültiges SQL ist,
> das nur von MySQL akzeptiert wird und dann irgendwas auslöst, das den
> meisten unklar ist.