Query Problem

Hallo,
ich habe eine Tabelle importiert die in etwa 17000 Einträge hat. Auf
dieser Tabelle sind bisher kein Primarykey oder ähnliches definiert
(Tabelle stammt aus einer VIEW). Die Struktur der Tabelle sieht so
aus:

y20y20s0 char(111) utf8_bin
y20gpnum char(60) utf8_bin
y21kname varchar(150) utf8_bin

Im Prinzip kann man aus der Spalte y20y20s0 einen Primary Key
erstellen da die Werte hier Unique sind. Nun geht es in meiner Abfrage
um doppelte Werte in der Spalte y20gpnum die ich in einer Liste
darstellen möchte. Es handelt sich hierbei um ca. 260 Einträge die
ausgegeben werden sollen

SELECT y20y20s0,y20gpnum,y21kname
FROM csy20oats WHERE y20gpnum IN
(SELECT y20gpnum
FROM csy20oats
GROUP BY y20gpnum
HAVING COUNT( * ) >1)
ORDER BY y20gpnum

Auf einer Oracle DB habe ich die gleiche Struktur, hier funktioniert
die Abfrage einwandfrei. Leider steigt bei der selben Abfrage in der
MySQL DB die CPU Last auf 100% und verbleibt dort auch, bis ich die DB
runterfahre.

Warum funktioniert die Abfrage hier nicht. Muss ich die Struktur
ändern oder zusätzliche Indiezes erstellen? Bin ein ziemlicher Newbie
was DBs angeht und wäre daher sehr Dankbar um euer Hilfe.

Info zur DB: Server Version: 5.0.45-community-nt
Danke

Re: Query Problem

Andreas Franke wrote:

> ich habe eine Tabelle importiert die in etwa 17000 Einträge hat. Auf
> dieser Tabelle sind bisher kein Primarykey oder ähnliches definiert
> (Tabelle stammt aus einer VIEW). Die Struktur der Tabelle sieht so
> aus:
>
> y20y20s0 char(111) utf8_bin
> y20gpnum char(60) utf8_bin
> y21kname varchar(150) utf8_bin
>
> Im Prinzip kann man aus der Spalte y20y20s0 einen Primary Key
> erstellen da die Werte hier Unique sind. Nun geht es in meiner Abfrage
> um doppelte Werte in der Spalte y20gpnum die ich in einer Liste
> darstellen möchte. Es handelt sich hierbei um ca. 260 Einträge die
> ausgegeben werden sollen
>
> SELECT y20y20s0,y20gpnum,y21kname
> FROM csy20oats WHERE y20gpnum IN
> (SELECT y20gpnum
> FROM csy20oats
> GROUP BY y20gpnum
> HAVING COUNT( * ) >1)
> ORDER BY y20gpnum

Hier kommen zwei Dinge zusammen:

1. keine Indexe. Alle Operationen sind Tablescans (also langsam)

2. eine Subquery mit IN. MySQL kann derzeit nicht zuverlässig
unterscheiden, ob die Subquery korreliert ist oder nicht.
Deswegen wird die Subquery vorsichtshalber für *jede* Zeile
der äußeren Tabelle ausgeführt. Das ist langsam!

Gegen 1. hilft ein Index auf y20gpnum:

ALTER TABLE csy20oats ADD INDEX (y20gpnum);

Dann sollte die Query schon deutlich schneller laufen.


Gegen 2. hilft folgende Änderung:

SELECT y20y20s0,y20gpnum,y21kname FROM csy20oats AS a
WHERE y20gpnum = (SELECT y20gpnum FROM csy20oats AS b
WHERE a.y20gpnum = b.y20gpnum
GROUP BY y20gpnum HAVING COUNT(*) > 1)
ORDER BY y20gpnum

Alternativ könnte man auch das Ergebnis der kompletten GROUP BY
Subquery in eine temporäre Tabelle schreiben und das IN gegen
diese temporäre Tabelle fahren.

> Auf einer Oracle DB habe ich die gleiche Struktur, hier funktioniert
> die Abfrage einwandfrei. Leider steigt bei der selben Abfrage in der
> MySQL DB die CPU Last auf 100% und verbleibt dort auch, bis ich die DB
> runterfahre.
>
> Warum funktioniert die Abfrage hier nicht.

Die Abfrage funktioniert nicht nicht. Sie dauert halt einfach sehr
lange. Zumindest länger als du warten wolltest. Ich vermute mal, in
Oracle ist obige Tabelle eine VIEW. Dann kann Oracle natürlich auch
Indexe der unterliegenden Tabellen benutzen.


XL

Re: Query Problem

Axel Schwenke schrieb:

> Andreas Franke wrote:
>
>> ich habe eine Tabelle importiert [...]
>> (Tabelle stammt aus einer VIEW). [...]

Wer lesen kann ...

> [...] Ich vermute mal, in
> Oracle ist obige Tabelle eine VIEW. [...]

.... ist klar im Vorteil ;-)

SCNR. Claus

Re: Query Problem

Axel Schwenke wrote:

>> SELECT y20y20s0,y20gpnum,y21kname
>> FROM csy20oats WHERE y20gpnum IN
>> (SELECT y20gpnum
>> FROM csy20oats
>> GROUP BY y20gpnum
>> HAVING COUNT( * ) >1)
>> ORDER BY y20gpnum
>
> Hier kommen zwei Dinge zusammen:
>
> 1. keine Indexe. Alle Operationen sind Tablescans (also langsam)
>
> 2. eine Subquery mit IN. MySQL kann derzeit nicht zuverlässig
> unterscheiden, ob die Subquery korreliert ist oder nicht.
> Deswegen wird die Subquery vorsichtshalber für *jede* Zeile
> der äußeren Tabelle ausgeführt. Das ist langsam!

Wie bitte?
Die Unterscheidung korreliert oder nicht ist ja wohl primitiv, wenn nicht mal das klappt, sollte man lieber keine Subqueries implementieren.
Und selbst korrelierte Subquery muss man nicht Row für Row ausführen, das haben DBMSe schon vor Jahren umgeschrieben und ent-korreliert.

> Gegen 2. hilft folgende Änderung:
>
> SELECT y20y20s0,y20gpnum,y21kname FROM csy20oats AS a
> WHERE y20gpnum = (SELECT y20gpnum FROM csy20oats AS b
> WHERE a.y20gpnum = b.y20gpnum
> GROUP BY y20gpnum HAVING COUNT(*) > 1)
> ORDER BY y20gpnum

Das Umschreiben einer Subquery in eine korrelierte Version hilft dem Optimizer?
Ich denke, der kann nicht unterscheiden?

> Alternativ könnte man auch das Ergebnis der kompletten GROUP BY
> Subquery in eine temporäre Tabelle schreiben und das IN gegen
> diese temporäre Tabelle fahren.

Oder die klassiche Lösung, von Hand ent-korreliert und mit Derived Table:

SELECT a.*
FROM csy20oats AS a
JOIN
(SELECT y20gpnum
FROM csy20oats
GROUP BY y20gpnum
HAVING COUNT(*) > 1) AS dt
WHERE a.y20gpnum = dt.y20gpnum
ORDER BY a.y20gpnum

Dieter

Re: Query Problem

Moin Dieter,

Dieter Noeth wrote:
> Axel Schwenke wrote:
>
>> 2. eine Subquery mit IN. MySQL kann derzeit nicht zuverlässig
>> unterscheiden, ob die Subquery korreliert ist oder nicht.
>> Deswegen wird die Subquery vorsichtshalber für *jede* Zeile
>> der äußeren Tabelle ausgeführt. Das ist langsam!
>
> Wie bitte?

Hörensagen. Ich weiß keine Details und will sie ehrlich gesagt
auch gar nicht wissen ;)

Subqueries sind ein ganz dunkles Kapitel und ständige Baustelle.

>> SELECT y20y20s0,y20gpnum,y21kname FROM csy20oats AS a
>> WHERE y20gpnum = (SELECT y20gpnum FROM csy20oats AS b
>> WHERE a.y20gpnum = b.y20gpnum
>> GROUP BY y20gpnum HAVING COUNT(*) > 1)
>> ORDER BY y20gpnum
>
> Das Umschreiben einer Subquery in eine korrelierte Version hilft dem Optimizer?

Jep. Zum einen nimmt $TERM = ($SUBQUERY) einen kürzeren Pfad als
$TERM IN ($SUBQUERY). Zum anderen muß die korrelierte Subquery
oben nur noch einen winzigen Bruchteil der Tabelle durchackern
statt die ganze. Ein ganz allgemeiner Optimierungstip für
Subqueries in MySQL ist, Bedingungen auf die äußere Tabelle
(auch redundant) in die Subquery mit reinzunehmen.


XL

Re: Query Problem

Egal ob korreliert oder nicht. Danke für die Antworten.
War auf jeden Fall hilfreich

Gruß