[Erledigt] MySQL Abfrage dauert lange (5 Sek), Optimierungsvorschläge?

[Gamemodder]

Hey,
leider muss ich bei einer Seite auf das Caching verzichten und folgende MySql Abfrage verhagelt mir die Stimmung...

                select * from (
                    SELECT @pos:=@pos+1 as rank, bzn, cnt from 
                    ( select @pos := 0 ) sqlvars,
                    (
                        select
                           bzn, count(*) AS cnt
                        from
                        (
                            SELECT U.uid, A.bzn FROM `fe_users` U
                            INNER JOIN tx_ervagenturen_domain_model_agenturen A ON A.agenturnummer = U.company

                            UNION ALL
                            SELECT userid, bzn FROM tx_ervselfies_domain_model_selfies 
                        ) a group by bzn
                        having count(*) > 1
                        ORDER BY cnt DESC
                    ) b
                    order by rank
                ) c
                order by $order 

Habt ihr eine Idee, was ich beschleunigen kann? Sinn dieser Abfrage ist die Erstellung eines Rankings von 26 Regionen (bzn) mit jeweiligen Usern.

[Tera3yte]

Paging?

[VPh]

Hast du ein paar Beispieldatensätze und die Tabellenstruktur (+ erwartetes Ergebnis für die Datensätze)?

[hausl]

Mach mal vor die query ein EXPLAIN und lass die laufen, ev. hilft das ja zur Analyse wo ev. konrekt Zeit auf der Strecke bleibt.

SELECT * sollte man z.B. grundsätzlich vermeiden: http://php-de.github.io/jumpto/code-smells/#select-

LG

[akretschmer]

Habt ihr eine Idee, was ich beschleunigen kann? Sinn dieser Abfrage ist die Erstellung eines Rankings von 26 Regionen (bzn) mit jeweiligen Usern.

Eine DB nutzen, die das auch ohne Klimmzüge kann.

[Gamemodder]

Hast du ein paar Beispieldatensätze und die Tabellenstruktur (+ erwartetes Ergebnis für die Datensätze)?

Also die Tabellen sehen wie folgt aus:

fe_user
userid, userdaten, company

agenturen
company (= fe_user company), bzn

selfies
bzn, userid


ich bekomme dann ausgegeben anzahl der selfies + user pro bzn mit entsprechendem ranking.

[Gamemodder]

P.S. Explain ergibt dieses Ergebnis:


id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 27 Using filesort
2 DERIVED <derived3> system NULL NULL NULL NULL 1 Using temporary; Using filesort
2 DERIVED <derived4> ALL NULL NULL NULL NULL 27
4 DERIVED <derived5> ALL NULL NULL NULL NULL 1209 Using temporary; Using filesort
5 DERIVED U ALL NULL NULL NULL NULL 1230
5 DERIVED A ALL NULL NULL NULL NULL 14590 Using where; Using join buffer
6 UNION tx_ervselfies_domain_model_selfies ALL NULL NULL NULL NULL 29
NULL UNION RESULT <union5,6> ALL NULL NULL NULL NULL NULL
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used

[akretschmer]

P.S. Explain ergibt dieses Ergebnis:

temporary und filesort. Dort findest Du Deine Zeit.

[erc]

leider muss ich bei einer Seite auf das Caching verzichten und folgende MySql Abfrage verhagelt mir die Stimmung...

Warum ist Caching nicht möglich?

Was du versuchen könntest ist gleich im inneren Query zu gruppieren (der nachfolgende dann mit SUM() statt COUNT()).

SELECT bzn, COUNT(A.bzn) AS cnt FROM `fe_users` U
INNER JOIN tx_ervagenturen_domain_model_agenturen A ON A.agenturnummer = U.company
GROUP BY bzn
UNION ALL
SELECT bzn, COUNT(bzn) AS cnt FROM tx_ervselfies_domain_model_selfies 
GROUP BY bzn 

*edit* Ich hab mir das EXPLAIN vorns gar nicht richtig angeschaut. Da ist doch weder auf A.agenturnummer (PK?!) noch auf U.company ein Index gesetzt. Für den Query ist ein Index auf eine der Spalte nötig.

SELECT * sollte man z.B. grundsätzlich vermeiden: http://php-de.github.io/jumpto/code-smells/#select-

Muss dieses unreflektierte blabla immer sein? Der Hinweis ist ja prinzipiell OK, aber er sollte dann schon passen.

[hausl]

SELECT * sollte man z.B. grundsätzlich vermeiden: http://php-de.github.io/jumpto/code-smells/#select-

Muss dieses unreflektierte blabla immer sein? Der Hinweis ist ja prinzipiell OK, aber er sollte dann schon passen.

Ich schrieb bewusst "grundsätzlich", hätte zumindest noch nie gehört das es dadruch besser würde. Aber ich lerne gerne dazu! Abgesehen davon halte ich es subjektiv in der query halt auch parktisch wegen lesbarkeit.

LG

[erc]

Herrlich... hauptsache "SELECT *" böse posten, aber nicht wissen warum.

Warum ist SELECT * schlecht?
-keine Übersicht welche Daten wirklich benötigt werden
-unnötige Daten werden u.U geladen
-änderungen am Schema können unverhersehbare Fehler auslösen (Namenskollision bei Joins/gelöscht oder umbenannte Spalten)

Das trift wie auf diesen Query zu? Gar nicht. Der äusserste Query mit dem SELECT * dreht nur die Sortierung.

[hausl]

Die Punkte kenn ich, danke ... für mich genau das...

keine Übersicht welche Daten wirklich benötigt werden

Sry, ich hab die query nicht im Detail seziert und auf Anhieb erkannt hab das das nur die Sortierung dreht. Ich mag gerne immer alles explizit angeführt... Seis drum.. zumindest war der Hinweis mit EXPLAIN wenigstens sinnvoll.

LG

[lstegelitz]

Sub-Selects werden für jeden "äußeren" Datensatz erneut durchgeführt, deswegen ist das wiederholte Verschachteln von Sub-Selects ein absoluter Performancekiller.
Der Explain zeigt auch, das sogut wie kein Index genutzt wird...

Multipliziere alle "rows" Angaben, dann hast du eine ungefähre Hausnummer, wieviele Datensätze durchgerödelt werden müssen (worst case).

[akretschmer]

Habt ihr eine Idee, was ich beschleunigen kann? Sinn dieser Abfrage ist die Erstellung eines Rankings von 26 Regionen (bzn) mit jeweiligen Usern.

Über wie viele Datensätze reden wir?

Um es mal für 3 User mit je 5 Records und on-the-fly erzeuugten Daten zu zeigen:

test=*# select x, val, rank() over (partition by x order by val desc) from (select x, (random()*1000)::int as val from generate_series(1,3)x cross join generate_Series(1,5) y) foo;
 x | val | rank
---+-----+------
 1 | 772 |    1
 1 | 337 |    2
 1 | 310 |    3
 1 |  56 |    4
 1 |   6 |    5
 2 | 686 |    1
 2 | 420 |    2
 2 | 400 |    3
 2 | 379 |    4
 2 | 254 |    5
 3 | 429 |    1
 3 | 422 |    2
 3 | 245 |    3
 3 | 158 |    4
 3 |  48 |    5
(15 rows)

Das nun mal mit Zeitmessung, 50 Usern und 500 Werten je User:

test=*# explain analyse select x, val, rank() over (partition by x order by val desc) from (select x, (random()*1000)::int as val from generate_series(1,50) x cross join generate_Series(1,500) y) foo;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=164516.85..184516.85 rows=1000000 width=8) (actual time=58.360..110.192 rows=25000 loops=1)
   ->  Sort  (cost=164516.85..167016.85 rows=1000000 width=8) (actual time=58.345..69.524 rows=25000 loops=1)
         Sort Key: x.x, (((random() * 1000::double precision))::integer)
         Sort Method: external sort  Disk: 440kB
         ->  Nested Loop  (cost=0.01..27510.01 rows=1000000 width=4) (actual time=0.088..26.329 rows=25000 loops=1)
               ->  Function Scan on generate_series x  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.013..0.024 rows=50 loops=1)
               ->  Function Scan on generate_series y  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.002..0.140 rows=500 loops=50)
 Total runtime: 116.966 ms
(8 rows)

500 User, 5000 Rows:

test=*# explain analyse select x, val, rank() over (partition by x order by val desc) from (select x, (random()*1000)::int as val from generate_series(1,500) x cross join generate_Series(1,5000) y) foo;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=164516.85..184516.85 rows=1000000 width=8) (actual time=6219.669..11306.013 rows=2500000 loops=1)
   ->  Sort  (cost=164516.85..167016.85 rows=1000000 width=8) (actual time=6219.659..7316.172 rows=2500000 loops=1)
         Sort Key: x.x, (((random() * 1000::double precision))::integer)
         Sort Method: external sort  Disk: 43976kB
         ->  Nested Loop  (cost=0.01..27510.01 rows=1000000 width=4) (actual time=0.723..2662.026 rows=2500000 loops=1)
               ->  Function Scan on generate_series x  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.070..0.357 rows=500 loops=1)
               ->  Function Scan on generate_series y  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.002..1.413 rows=5000 loops=500)
 Total runtime: 11933.645 ms
(8 rows)

Würde ich work_mem höher nehmen wäre das noch schneller weil dann im RAM, jetzt hat er auf Platte sortiert. Billiger PC, ca. 7 Jahre alt.

[lstegelitz]

Über wie viele Datensätze reden wir?

Im worst case: 27 * 1 * 27 *1209 * 1230 * 14590 * 29