Subselect bringt 100% CPU Last?

Tag.

Umgebung
--------
OS: Gentoo Linux mit Kernel 2.6.18
HW: Athlon 2600, 1GB RAM
MySQL: 5.0.26
User: MySQL Anfänger, Linux halbwegs fit

Daten
-----
Gegeben sei eine Tabelle mit 50K+ Einträgen, Aufträge aus einem Webshop.
Spalten (vereinfacht): Auftragsnummer, Land, Auftragssumme.
Indizes: Auftragsnummer, Land (u.a.)

Ziel
----
Ich möchte den mittleren Auftragswert aller Aufträge der 10 umsatzstärksten
Länder, gruppiert nach Land, absteigend sortiert.

Ansatz
------
Da denke ich mir das ein Subselect hilft (geht sicher auch anders, ich bin
offen für Vorschläge).

SELECT `land`, AVG(`sum`) AS `avg`
FROM `order`
WHERE `land` IN (
SELECT `land`
FROM `order`
GROUP BY `land`
ORDER BY SUM(`sum`) DESC
LIMIT 0,10
)
GROUP BY `land`
ORDER BY `avg` DESC;

Resultat
--------
Das bringt mir den Fehler:
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME
subquery'

Ok, dann nehme ich das LIMIT raus, bekomme die Werte für alle Länder (ca.
70) und werde die TOP10 im Programm selber rausfummeln.

Das bringt mir 100% CPU Auslastung. Meine Geduld dauerte ca. 5 Minuten, dann
habe ich den mysqld abgeschossen. Geswapt wurde in der Zeit nicht, kein
Disk-IO, nur 100% CPU-Last.


Bisher probiert um das Problem selber zu lösen:
-----------------------------------------------
- Das eingeschobene SELECT alleine ausprobiert um zu sehen ob dass das
gewünscht liefert. Tut es, Tabelle der 10 umsatzstärksten Länder,
absteigend sortiert.

- Getestet dass das äussere SELECT ohne das Subselect (z.B. mit WHERE `land`
LIKE 'D') funktioniert. Tut es.

- Subselect nicht mit WHERE IN sondern mit WHERE = umformuliert,
liefert '#1242 - Subquery returns more than 1 row'. Ok, verständlich.

- Abfrage mal nicht mit phpMyAdmin, sondern direkt in den Monitor (mysql)
getippt, gleiches Resultat. (Ich weiss das phpMyAdmin 'bäh' ist, ich lese
hier schon 'ne Weile mit.)

- Mit EXPLAIN SELECT ... geguckt ob das sehr schlimm ist, was ich da mache.
+----+--------------------+--------+-------+---------------+ ------+---------+------+-------+---------------------------- ------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+--------------------+--------+-------+---------------+ ------+---------+------+-------+---------------------------- ------------------+
| 1 | PRIMARY | order2 | ALL | NULL | NULL |
NULL | NULL | 31673 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | order2 | index | NULL | onum |
25 | NULL | 31673 | Using index; Using temporary; Using filesort |
+----+--------------------+--------+-------+---------------+ ------+---------+------+-------+---------------------------- ------------------+
2 rows in set (0.00 sec)

Ok, die primary query ist vom Type 'ALL', nicht schön, aber wie geht's
besser?


- Online-Doc gelesen, nix gefunden.

- Kofler, 'MySQL 5.0', 3.Aufl. gelesen, keinen Hinweis gefunden.

- Gegoogelt, Millionen von Hits bekommen, ich weiss offensichtlich nicht
nach was ich suchen soll.


Fragen:
-------
- Ist dieses Verhalten von MySQL normal (100% CPU)?
- Wie sähe eine bessere (funktionierende) Abfrage aus, die das gewünschte
Resultat liefert?

Die übliche Frage, wie ich da alleine hätte drauf kommen können, kann ich
mir selber beantworten: SQL lernen. Da bin ich dabei.

Danke.

Ekki

Re: Subselect bringt 100% CPU Last?

Ekki Plicht (DF4OR) wrote:

> Das bringt mir 100% CPU Auslastung. Meine Geduld dauerte ca. 5 Minuten,
> dann habe ich den mysqld abgeschossen.

Fürs nächste Mal reicht

mysqladmin show processlist
mysqladmin kill <...>

oder in MySQL:
Show [full] processlist
kill <...>


> - Mit EXPLAIN SELECT ... geguckt ob das sehr schlimm ist, was ich da
> mache.

Ergebnis: ja, ist es. ;-)


> Ok, die primary query ist vom Type 'ALL', nicht schön, aber wie geht's
> besser?

Das ist weniger das Problem - wenn er summieren und Mittelwert bilden
soll, muß er nunmal alle Datensätze anfassen. 1x ist vermutlich auch ok.

Schlimmer ist, daß bei *beiden* Queries 31673 Rows angefaßt werden
müssen. Hast Du einen Index auf land?

Weitere Möglichkeit: Versuch mal, den Subselect als Tabelle einzubinden:

SELECT `land`, AVG(`sum`) AS `avg`
FROM `order`
JOIN (
SELECT `land`
FROM `order`
GROUP BY `land`
ORDER BY SUM(`sum`) DESC
LIMIT 0,10
) AS sub USING (land)
GROUP BY `land`
ORDER BY `avg` DESC;

(ungetestet!)

Damit sollte der Subselect nur 1x ausgewertet werden. Index auf land ist
da natürlich auch Pflicht, wegen dem JOIN.


> - Ist dieses Verhalten von MySQL normal (100% CPU)?

Ja, wenn ein solcher Query abgesetzt wird, bei dem 31673^2 Kombinationen
durchprobiert werden, schon. Das ist eben eine sehr rechenintensiver
Vorgang. Deshalb sind die Indices ja so wichtig. Zudem kann es sein (da
bin ich mir nicht ganz sicher), daß ein Subqoery mit =/IN/... für jede
"äußere" Zeile nochmal ausgeführt wird - das wäre natürlich tödlich.
Daher mein Alternativvorschlag.


Thomas

Re: Subselect bringt 100% CPU Last?

Thomas Rachel wrote:

> Ekki Plicht (DF4OR) wrote:
>> - Mit EXPLAIN SELECT ... geguckt ob das sehr schlimm ist, was ich da
>> mache.
>
> Ergebnis: ja, ist es. ;-)
>
>
>> Ok, die primary query ist vom Type 'ALL', nicht schön, aber wie geht's
>> besser?
>
> Das ist weniger das Problem - wenn er summieren und Mittelwert bilden
> soll, muß er nunmal alle Datensätze anfassen. 1x ist vermutlich auch ok.
>
> Schlimmer ist, daß bei *beiden* Queries 31673 Rows angefaßt werden
> müssen. Hast Du einen Index auf land?

Ja, Index ist auf 'land' (u.a.).

> Weitere Möglichkeit: Versuch mal, den Subselect als Tabelle einzubinden:
>
> SELECT `land`, AVG(`sum`) AS `avg`
> FROM `order`
> JOIN (
> SELECT `land`
> FROM `order`
> GROUP BY `land`
> ORDER BY SUM(`sum`) DESC
> LIMIT 0,10
> ) AS sub USING (land)
> GROUP BY `land`
> ORDER BY `avg` DESC;
>
> (ungetestet!)

Geht, super, ich danke Dir!

> Damit sollte der Subselect nur 1x ausgewertet werden. Index auf land ist
> da natürlich auch Pflicht, wegen dem JOIN.
>
>
>> - Ist dieses Verhalten von MySQL normal (100% CPU)?
>
> Ja, wenn ein solcher Query abgesetzt wird, bei dem 31673^2 Kombinationen
> durchprobiert werden, schon. Das ist eben eine sehr rechenintensiver
> Vorgang. Deshalb sind die Indices ja so wichtig. Zudem kann es sein (da
> bin ich mir nicht ganz sicher), daß ein Subqoery mit =/IN/... für jede
> "äußere" Zeile nochmal ausgeführt wird - das wäre natürlich tödlich.
> Daher mein Alternativvorschlag.

Ich hab mir das gestern abend schon fast gedacht, war aber wohl zu müde
auszurechnen was 31k² zu testende Kombinationen bedeutet...

Also, schönen Dank nochmal.

Gruß,
Ekki

Re: Subselect bringt 100% CPU Last?

"Ekki Plicht (DF4OR)" wrote:

> Gegeben sei eine Tabelle mit 50K+ Einträgen, Aufträge aus einem Webshop.
> Spalten (vereinfacht): Auftragsnummer, Land, Auftragssumme.
> Indizes: Auftragsnummer, Land (u.a.)
>
> Ziel
> ----
> Ich möchte den mittleren Auftragswert aller Aufträge der 10 umsatzstärksten
> Länder, gruppiert nach Land, absteigend sortiert.
>
> Ansatz
> ------
> Da denke ich mir das ein Subselect hilft (geht sicher auch anders, ich bin
> offen für Vorschläge).
>
> SELECT `land`, AVG(`sum`) AS `avg`
> FROM `order`
> WHERE `land` IN (
> SELECT `land`
> FROM `order`
> GROUP BY `land`
> ORDER BY SUM(`sum`) DESC
> LIMIT 0,10
> )
> GROUP BY `land`
> ORDER BY `avg` DESC;

Mach es doch so:

SELECT land, mittel FROM
( SELECT land,
AVG(sum) AS mittel,
SUM(sum) AS total,
FROM order
GROUP BY land
ORDER BY total DESC
LIMIT 10
) ORDER BY mittel DESC


XL

Re: Subselect bringt 100% CPU Last?

Ekki Plicht (DF4OR)!

> Ich möchte den mittleren Auftragswert aller Aufträge der 10 umsatzstärksten
> Länder, gruppiert nach Land, absteigend sortiert.

Hilft dir das hier weiter?

select land, avg(sum) from order group by land order by sum(sum) desc limit 10;

> Ok, die primary query ist vom Type 'ALL', nicht schön, aber wie geht's
> besser?

Um ALL wirst du nicht rum kommen, um die Summen auszurechnen müssen auf
jeden Fall alle Zeilen gelesen werden. Das "index" bei deinem äußeren
Query ist auch trügerisch, da der innere ja wieder jedes Land
zurückliefert, und dann müssen sie auch alle gelesen werden, nur dass er
das Land zu dem er die Daten will immer gleich findet ;-)



Gruß
Daniel

Re: Subselect bringt 100% CPU Last?

Axel Schwenke wrote:

> "Ekki Plicht (DF4OR)" wrote:
>
>> Gegeben sei eine Tabelle mit 50K+ Einträgen, Aufträge aus einem
>> Webshop. Spalten (vereinfacht): Auftragsnummer, Land, Auftragssumme.
>> Indizes: Auftragsnummer, Land (u.a.)
>>
>> Ziel
>> ----
>> Ich möchte den mittleren Auftragswert aller Aufträge der 10
>> umsatzstärksten Länder, gruppiert nach Land, absteigend sortiert.
>>
>> Ansatz
>> ------
>> Da denke ich mir das ein Subselect hilft (geht sicher auch anders, ich
>> bin offen für Vorschläge).
>>
>> SELECT `land`, AVG(`sum`) AS `avg`
>> FROM `order`
>> WHERE `land` IN (
>> SELECT `land`
>> FROM `order`
>> GROUP BY `land`
>> ORDER BY SUM(`sum`) DESC
>> LIMIT 0,10
>> )
>> GROUP BY `land`
>> ORDER BY `avg` DESC;
>
> Mach es doch so:
>
> SELECT land, mittel FROM
> ( SELECT land,
> AVG(sum) AS mittel,
> SUM(sum) AS total,
> FROM order
> GROUP BY land
> ORDER BY total DESC
> LIMIT 10
> ) ORDER BY mittel DESC

Das bringt mir '#1248 - Every derived table must have its own alias '. Ich
habe die Ursache dafür noch nicht gefunden.

Gruß,
Ekki

Re: Subselect bringt 100% CPU Last?

Daniel Fischer wrote:

> Ekki Plicht (DF4OR)!
>
>> Ich möchte den mittleren Auftragswert aller Aufträge der 10
>> umsatzstärksten Länder, gruppiert nach Land, absteigend sortiert.
>
> Hilft dir das hier weiter?
>
> select land, avg(sum) from order group by land order by sum(sum) desc
> limit 10;

Oja, das geht auch, danke.
Thomas' Vorschlag liefert das Resultat auch noch gleich sortiert nach
avg(sum), aber das bekomme ich bei deiner Abfrage wohl auch noch hin.
Ausserdem hatte ich in meinem Ursprungsposting die Ausgabereihenfolge nicht
definiert.


Danke & Gruß,
Ekki

Re: Subselect bringt 100% CPU Last?

"Ekki Plicht (DF4OR)" wrote:
> Axel Schwenke wrote:
>
>> SELECT land, mittel FROM
>> ( SELECT land,
>> AVG(sum) AS mittel,
>> SUM(sum) AS total,
>> FROM order
>> GROUP BY land
>> ORDER BY total DESC
>> LIMIT 10
>> ) ORDER BY mittel DESC
>
> Das bringt mir '#1248 - Every derived table must have its own alias '. Ich
> habe die Ursache dafür noch nicht gefunden.

SELECT land, mittel FROM
( SELECT land,
AVG(sum) AS mittel,
SUM(sum) AS total,
FROM order
GROUP BY land
ORDER BY total DESC
LIMIT 10
) AS foobar ORDER BY mittel DESC


XL

Re: Subselect bringt 100% CPU Last?

Ekki Plicht (DF4OR)!

> Thomas' Vorschlag liefert das Resultat auch noch gleich sortiert nach
> avg(sum), aber das bekomme ich bei deiner Abfrage wohl auch noch hin.

Oh, ich hatte dein SQL nicht gelesen, nur den Text. Ja, kriegst du bei der
Abfrage auch hin, das Ergebnis sollte dann ungefähr so aussehen wie bei
Axel.


Gruß
Daniel