Langsame Query

Hallo zusammen,

nach einiger Recherche zu Indices, Performance, Joins,... bin ich nun
leider am Ende meines SQL-Lateins angelangt und hoffe auf das mir hier
jemand helfen kann.
Zu meinem Problem:
Es handelt sich um 3 Tabellen.
Zum einen gibt es eine datenerfassungstabelle, in der Dinge wie das
Alter, Geschlecht, Beruf,... gespeichert sind.
Zum anderen eine Fragentabelle, in der Fragentexte gespeichert sind.
Diese beiden Tabellen werden nun über eine Tabelle antworten
miteinander verknüpft. In der Antwort Tabelle steht also in jeder
Reihe die datenerfassungs_id, fragen_id und die antwort (im
Schulnotensystem 1-6)
In der Datenerfassungstabelle sind z.Z. ca. 300.000 Datensätze und
inder Antwort Tabelle ca. 6 Millionen.
Die Abfragen um die es geht sind z.B. der Art:
Gib mir die Anzahl der verschiedenen Anworten pro Frage für alle
Kunden im Alter zwischen 18 und 25.
Um es in SQL auszudrücken:

SELECT answers.q_id, answers.answer, count(answers.answer)
FROM answers
INNER JOIN datenerfassung ON datenerfassung.id =3D answers.d_id
WHERE datenerfassung.age BETWEEN 18 AND 25
GROUP BY answers.q_id, answers.answer

Problem ist nun, diese Abfrage dauert an die 20 Sekunden. Da ich aber
für einen Export der Daten ca. 250 Abfragen dieser Art machen muss,
ist es in der Summe dann doch ein wenig länger ;-)

Was ich bisher versucht habe:
Ich habe mich erstmal sehr an www.hackmysql.com orientiert. Leider mit
mäßigem Erfolg (Was nicht an der Seite, sondern eher an mir liegt ;)
Zuerst habe ich versucht das GROUP BY mit einem Index über die beiden
Gruppierten Spalten zu optimieren. Das hat auch Funktioniert, nun habe
ich aber das Problem, das in der Antwort Tabelle nicht mehr der Index
über die Datenerfassungsids benutzt wird. Das wiederum hat zur Folge,
dass immer alle der ca. 6 Mio. Datensätze angefasst werden müssen. Und
hier liegt nun letztendlich mein Problem:
kann diese Abfrage noch groß verbessert werden, oder muss ich einfach
damit leben, dass nur einer der Indices benutzt werden kann? Oder gibt
es noch ganz andere Herangehensweisen?

Danke auf jeden Fall fürs Lesen! Falls noch Infos fehlen, gebt
bescheid, ich werde dann Versuchen alles so gut es geht
aufzubereiten.

Danke und Gruß
Micha

Re: Langsame Query

plastic wrote:
^^^^^^^
Hier gehört dein Realname hin. Dann lesen dich mehr Leute.

> In der Datenerfassungstabelle sind z.Z. ca. 300.000 Datensätze und
> inder Antwort Tabelle ca. 6 Millionen.
> Die Abfragen um die es geht sind z.B. der Art:
> Gib mir die Anzahl der verschiedenen Anworten pro Frage für alle
> Kunden im Alter zwischen 18 und 25.
> Um es in SQL auszudrücken:
>
> SELECT answers.q_id, answers.answer, count(answers.answer)
> FROM answers
> INNER JOIN datenerfassung ON datenerfassung.id = answers.d_id
> WHERE datenerfassung.age BETWEEN 18 AND 25
> GROUP BY answers.q_id, answers.answer
>
> Problem ist nun, diese Abfrage dauert an die 20 Sekunden. Da ich aber
> für einen Export der Daten ca. 250 Abfragen dieser Art machen muss,
> ist es in der Summe dann doch ein wenig länger ;-)
>
[snip]
>
> Danke auf jeden Fall fürs Lesen! Falls noch Infos fehlen, gebt
> bescheid, ich werde dann Versuchen alles so gut es geht
> aufzubereiten.

In jedem Fall fehlt das EXPLAIN für die Abfrage.

Ich habe zuletzt mit ähnlichen Abfragen zu tun gehabt. Geholfen hat dort
ein kombinierter Index über
(JOIN-Bedingung, GROUP BY Felder)
Also:
CREATE INDEX (d_id, q_id, answer)

Außerdem solltest du COUNT(*) schreiben, um dem Optimizer eine höhere
Entscheidungsfreiheit zu geben, wie erzählt.

Dann sollte das GROUP BY komplett per index scan abgearbeitet und damit
schnell werden. Den obligatorischen Index über datenerfassung.age wirst
du ja haben.

Grüße
Kai


--
This signature is left as an exercise for the reader.

Re: Langsame Query

On 12 Jun., 23:31, Kai Ruhnau
wrote:
> plastic wrote:
>
> ^^^^^^^
> Hier gehört dein Realname hin. Dann lesen dich mehr Leute.
>
>
>
>
>
> > In der Datenerfassungstabelle sind z.Z. ca. 300.000 Datensätze und
> > inder Antwort Tabelle ca. 6 Millionen.
> > Die Abfragen um die es geht sind z.B. der Art:
> > Gib mir die Anzahl der verschiedenen Anworten pro Frage für alle
> > Kunden im Alter zwischen 18 und 25.
> > Um es in SQL auszudrücken:
>
> > SELECT answers.q_id, answers.answer, count(answers.answer)
> > FROM answers
> > INNER JOIN datenerfassung ON datenerfassung.id =3D answers.d_id
> > WHERE datenerfassung.age BETWEEN 18 AND 25
> > GROUP BY answers.q_id, answers.answer
>
> > Problem ist nun, diese Abfrage dauert an die 20 Sekunden. Da ich aber
> > für einen Export der Daten ca. 250 Abfragen dieser Art machen muss,
> > ist es in der Summe dann doch ein wenig länger ;-)
>
> [snip]
>
> > Danke auf jeden Fall fürs Lesen! Falls noch Infos fehlen, gebt
> > bescheid, ich werde dann Versuchen alles so gut es geht
> > aufzubereiten.
>
> In jedem Fall fehlt das EXPLAIN für die Abfrage.
>
> Ich habe zuletzt mit ähnlichen Abfragen zu tun gehabt. Geholfen hat dort
> ein kombinierter Index über
> (JOIN-Bedingung, GROUP BY Felder)
> Also:
> CREATE INDEX (d_id, q_id, answer)
>
> Außerdem solltest du COUNT(*) schreiben, um dem Optimizer eine höhere
> Entscheidungsfreiheit zu geben, wie erzählt.
>
> Dann sollte das GROUP BY komplett per index scan abgearbeitet und damit
> schnell werden. Den obligatorischen Index über datenerfassung.age wirst
> du ja haben.
>
> Grüße
> Kai
>
> --
> This signature is left as an exercise for the reader.

Hallo Kai,

vielen Dank schonmal für Deine Hilfe!
Bevor ich das Explain poste muss ich noch eine Sache veraus schicken:
das count auf answer existiert in meiner echten Abfrage nicht, ich
dachte nur so würde es mir leichter fallen das Ganze zu erklären.
In Wahrheit ist zu jeder Antwort eine Gewichtung gespeichert und diese
Gewichtungen werden aufaddiert, so dass eine Antwort mit einem Gewicht
von 0.5 nur zur Hälfte ins Gewicht fällt ;-)

Die Query sieht also wie folgt aus:

SELECT answers.q_id, answers.answer, sum( answers.gewicht )
FROM answers
INNER JOIN datenerfassung ON datenerfassung.id =3D answers.d_id
WHERE datenerfassung.age
BETWEEN 18
AND 25
GROUP BY answers.q_id, answers.answer

Den Index habe ich, wie von Dir geraten hinzugefügt, der Index über
age war, wie richtig vermutet, bereits gesetzt. Hier das EXPLAIN:
(Das EXPLAIN ist aus meiner Testumgebung, hier befinden sich nur
600.624 Antworten und ca. 40.000 Daterfassungsdatensätze in den beiden
Tabellen)

id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE answers ALL answer_index NULL NULL NULL 600624 Using
temporary; Using filesort
1 SIMPLE datenerfassung eq_ref PRIMARY,age,id PRIMARY 4
sgvht_test.answers.d_id 1 Using where


Mit einem RIght Join komme ich auf folgendes Ergebnis:

id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE datenerfassung ALL age NULL NULL NULL 40039 Using
where; Using temporary; Using filesort
1 SIMPLE answers ref answer_index answer_index 4
sgvht_test.datenerfassung.id 16


Ich muss noch dazu sagen das meine Testdatenbank mit einem Extremwert
spielt (der in der Realität durchaus vorkommen kann), hier sind
momentan mehr als 39.000 Einträge in der Datenerfassungstabelle mit
einem Alter von 24 Jahren versehen. (Das wird wohl der Grund sein,
warum der Index age hier nicht genommen wird.)

Mal wieder Danke fürs Lesen!

Micha

Re: Langsame Query

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: Langsame Query

On Jun 13, 9:30 pm, Andreas Kretschmer
wrote:
> begin plastic wrote:
>
> > On 12 Jun., 23:31, Kai Ruhnau
> > wrote:
>
> Lern quoten. Lern mehr über die Bedeutung des Begriffes
> EinleitungsZEILE.
>
> >> plastic wrote:
>
> >> ^^^^^^^
> >> Hier gehört dein Realname hin. Dann lesen dich mehr Leute.
>
> Lern lesen.
>
> > id select_type table type possible_keys key key_len=
ref rows
> > Extra
>
> Lern Deinen Editor zu bedienen, damit sowas nicht passiert.
>
> end
> 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)


On Jun 13, 9:30 pm, Andreas Kretschmer
wrote:
> begin plastic wrote:
>
> > On 12 Jun., 23:31, Kai Ruhnau
> > wrote:
>
> Lern quoten. Lern mehr über die Bedeutung des Begriffes
> EinleitungsZEILE.
>
> >> plastic wrote:
>
> >> ^^^^^^^
> >> Hier gehört dein Realname hin. Dann lesen dich mehr Leute.
>
> Lern lesen.
>
> > id select_type table type possible_keys key key_len=
ref rows
> > Extra
>
> Lern Deinen Editor zu bedienen, damit sowas nicht passiert.
>
> end
> 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)

Hallo Andreas,
danke für die netten Ratschläge. Das mit dem Namen klappt nun
hoffentlich, da kamen offensichtlich mein Unvermögen zu lesen und mein
technisches Unverständnis zusammen.
Eine Frage habe ich noch zu "Lern Deinen Editor zu bedienen, damit
sowas nicht passiert."
Was meinst Du mit "sowas"?

Micha

Re: Langsame Query

Am 14.06.2007 16:16 schrieb Michael Wielpütz:

Eins vorab: Lies und beherzige bitte http://learn.to/quote. Es ist
ausgesprochen lästig, wenn Du zig Zeilen zitierst, nur um am Ende dann
einmal
> danke für die netten Ratschläge. Das mit dem Namen klappt nun

zu sagen. Das will niemand wissen. Schau Dir mal an, wie hier
üblicherweise geantwortet und zitiert wird.

> hoffentlich, da kamen offensichtlich mein Unvermögen zu lesen und mein
> technisches Unverständnis zusammen.
> Eine Frage habe ich noch zu "Lern Deinen Editor zu bedienen, damit
> sowas nicht passiert."
> Was meinst Du mit "sowas"?

Mit "sowas" meint Andreas vermutlich, dass eine mehrfach unleserlich
umbrochene Ausgabe von EXPLAIN eben genau das ist - unleserlich.
Benutze einfach in mysql \G, um Deine Eingabe abzuschließen. Schon
meckert hier keiner mehr.

Noch ein Tipp: Versuch' Dich mit den Werkzeugen, die Du tagein, tagaus
benutzt vertraut zu machen. Möglichst, bevor Du damit auf die
Menschheit losgehst. Das betrifft mysql genauso wie Dein Newsprogramm.

Re: Langsame Query

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