Subquery Geschwindigkeit, Verstaendnisproblem

Hallo,

habe hier unter Mysql (4.1.11-Debian_4sarge7-log) eine Datenbank
vorliegen, bei der ich etwas auf den Schlauch stehe, was das
effektive Formulieren einer Anfrage angeht. Ich habe das
momentan mit einer Subquery gelöst:

SELECT Kegler, COUNT(Datum) AS Teiln FROM Endergebnisse=20
WHERE Endergebnisse.Kegler NOT IN (
SELECT Kegler FROM Endergebnisse
WHERE Datum >=3D '2006-01-27'
GROUP BY Kegler
HAVING COUNT(Datum) >=3D2 )
GROUP BY Kegler
HAVING COUNT(Datum) > 1
ORDER BY Teiln DESC;

Das Datum hier ist nur ein Beispiel. Für meine Tabelle [1460
Zeilen, 4 Spalten (Datum YYYY-MM-DD, Name, Ergebnis, GR), ein
Beispiel weiter unten] braucht diese Anfrage 22 Sekunden.

Das innere SELECT läuft in < 1 Sekunde (und liefert als Ergebnis
14 Namen). Füttere ich das äußere SELECT mit diesen Namen via

WHERE Endergebnisse.Kegler NOT IN ('Name1', 'Name2', 'Name3', ..., 'Name1=
4')

braucht die Anfrage ebenfalls weniger als 1 Sekunde.

Woher kommt dieser Zeitunterschied? Was kann ich tun?

Danke und Gruß,
Uwe

PS1: Als Beispiel mal ein paar Zeilen...
SELECT * FROM Endergebnisse
WHERE Datum > '2006-01-26' AND Datum < '2006-02-28'

Datum Kegler Ergebnis GR
2006-01-27 Schimpi 180
2006-01-27 Uwe 220
2006-01-27 Doelle 109
2006-01-27 Mike 77 R
2006-01-27 Falk 135
2006-01-27 Tobias 223 G
2006-01-27 Maribel 81
2006-02-24 Schimpi 158
2006-02-24 Uwe 200
2006-02-24 Doelle 82 R
2006-02-24 Marc 212 G
2006-02-24 Maribel 99
2006-02-24 Kermit 116

Zu jedem Datum gibt es mehrere Namen; zu einem Datum einen Namen aber
genau einmal; zu jedem Namen ein Datum oder mehrere Daten. Design
der Tabelle stammt nicht von mir, habe nur Leserechte auf selbige.

PS2: Ich möchte also alle Kegler die mindestens zwei Teilnahmen
haben sortiert nach Teilnahmen, aber ohne die, welche bei den
letzten x Terminen mindestens zwei mal dabei waren.

Re: Subquery Geschwindigkeit, Verstaendnisproblem

Uwe Pierau wrote:


> habe hier unter Mysql (4.1.11-Debian_4sarge7-log) eine Datenbank
> vorliegen, bei der ich etwas auf den Schlauch stehe, was das
> effektive Formulieren einer Anfrage angeht. Ich habe das
> momentan mit einer Subquery gelöst:

[snip]

> Das Datum hier ist nur ein Beispiel. Für meine Tabelle [1460
> Zeilen, 4 Spalten (Datum YYYY-MM-DD, Name, Ergebnis, GR), ein
> Beispiel weiter unten] braucht diese Anfrage 22 Sekunden.

Das liegt mit ziemlicher Sicherheit

a) an nichtnormalisierter Verknüpfung (eine Verknüpfung anhand eines
ID-Feldes geht wesentlich schneller als aufgrund eines Strings/varchar)

aber vor allem vermutlich an

b) fehlenden Indices.


a) ist IMHO vernachlässigbar, es dürfte vor allem an b) liegen.


> Das innere SELECT läuft in < 1 Sekunde (und liefert als Ergebnis
> 14 Namen).

Weil sie nur 1x durchlaufen zu werden braucht.


> Füttere ich das äußere SELECT mit diesen Namen via
>
> WHERE Endergebnisse.Kegler NOT IN ('Name1', 'Name2', 'Name3', ...,
> 'Name14')
>
> braucht die Anfrage ebenfalls weniger als 1 Sekunde.

Dito.


> Woher kommt dieser Zeitunterschied?

Wie gesagt, von einem fehlenden Index auf Kegler.


> Was kann ich tun?

ALTER TABLE Endergebnisse ADD INDEX (Kegler)


> PS1: Als Beispiel mal ein paar Zeilen...
> SELECT * FROM Endergebnisse
> WHERE Datum > '2006-01-26' AND Datum < '2006-02-28'

[...]

Wenig aussagekräftig. 13 Zeilen; für jede Zeile werden in der inneren
Tabelle nochmal 13 Zeilen durchlaufen, macht 169 Durchläufe. Das ist
auch ohne Index flott.

Bei Deinen originalen 1460 Zeilen sind es schon 2131600 Ergebniszeilen,
da sind 22 Sekunden IMHO schon wenig.

> Zu jedem Datum gibt es mehrere Namen; zu einem Datum einen Namen aber
> genau einmal; zu jedem Namen ein Datum oder mehrere Daten. Design
> der Tabelle stammt nicht von mir, habe nur Leserechte auf selbige.

Auch Einspruchsrechte / Korrekturmöglichkeit?

Wenn nein, hilft vielleicht ein

CREATE TEMPORARY TABLE E1 (INDEX (Kegler))) SELECT * FROM Endergebnisse
CREATE TEMPORARY TABLE E2 (INDEX (Kegler))) SELECT * FROM Endergebnisse

und dann mit E1 und E2 arbeiten (leider kann man in eine Query jede
Temporary Table nur 1x verwenden)

Evtl. reicht auch eine Temporary Table aus, wenn Du außen mit der
Originaltabelle arbeitest.

Oder aber ein
CREATE TEMPORARY TABLE E1 (INDEX (Kegler)))
SELECT Kegler FROM Endergebnisse
WHERE Datum >= '2006-01-27'
GROUP BY Kegler
HAVING COUNT(Datum) >=2;

SELECT E.Kegler, COUNT(Datum) AS Teiln FROM Endergebnisse E LEFT JOIN E1
USING (Kegler)
WHERE E1.Kegler IS NULL
GROUP BY E.Kegler
HAVING COUNT(Datum) > 1
ORDER BY Teiln DESC;


Habe die Performance jetzt nicht getestet; jeweils ein EXPLAIN davor
sollte Näheres ausspucken können.


Thomas
--
Viele Probleme erledigen sich von selbst, wenn man sie nicht dabei stört.
(Ernst Waldbrunn)

Re: Subquery Geschwindigkeit, Verstaendnisproblem

Uwe Pierau wrote:

> SELECT Kegler, COUNT(Datum) AS Teiln FROM Endergebnisse
> WHERE Endergebnisse.Kegler NOT IN (
> SELECT Kegler FROM Endergebnisse
> WHERE Datum >= '2006-01-27'
> GROUP BY Kegler
> HAVING COUNT(Datum) >=2 )
> GROUP BY Kegler
> HAVING COUNT(Datum) > 1
> ORDER BY Teiln DESC;
....
> PS2: Ich möchte also alle Kegler die mindestens zwei Teilnahmen
> haben sortiert nach Teilnahmen, aber ohne die, welche bei den
> letzten x Terminen mindestens zwei mal dabei waren.

Das geht ohne Subquery/Join:

SELECT Kegler, COUNT(*) AS Teiln
FROM Endergebnisse
GROUP BY Kegler
HAVING COUNT(*) > 1
AND COUNT(CASE WHEN Datum >= '2006-01-27' THEN Datum END) < 2
ORDER BY Teiln DESC;

Dieter

Re: Subquery Geschwindigkeit, Verstaendnisproblem

Uwe Pierau schrieb:
> ... Ich habe das
> momentan mit einer Subquery gelöst:
>=20
> SELECT Kegler, COUNT(Datum) AS Teiln FROM Endergebnisse=20
> WHERE Endergebnisse.Kegler NOT IN (
> SELECT Kegler FROM Endergebnisse
> WHERE Datum >=3D '2006-01-27'
> GROUP BY Kegler
> HAVING COUNT(Datum) >=3D2 )
> GROUP BY Kegler
> HAVING COUNT(Datum) > 1
> ORDER BY Teiln DESC;
>=20
> ... braucht diese Anfrage 22 Sekunden.

.. where in (select ...
wird vom Mysql offenbar zu Fuß ausgewertet, Dazu gibt es einen Haufen=20
Nachrichten im Netz. Wenn du es auf Subquery umschreibst (siehe anderes=20
Posting) wird es schnell funktionieren.
Aber natürlich formuliert man ein Subselect leichter .

W