Ankündigung

Einklappen
Keine Ankündigung bisher.

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

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

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

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

    PHP-Code:
                    select from (
                        
    SELECT @pos:=@pos+as rankbzncnt from 
                        
    select @pos := sqlvars,
                        (
                            
    select
                               bzn
    count(*) AS cnt
                            from
                            
    (
                                
    SELECT U.uidA.bzn FROM `fe_usersU
                                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.


  • #2
    Paging?
    - Laravel

    Kommentar


    • #3
      Hast du ein paar Beispieldatensätze und die Tabellenstruktur (+ erwartetes Ergebnis für die Datensätze)?
      Relax, you're doing fine.
      RTFM | php.de Wissenssammlung | Datenbankindizes | Dateien in der DB?

      Kommentar


      • #4
        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
        Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
        PHP.de Wissenssammlung | Kein Support per PN

        Kommentar


        • #5
          Zitat von Gamemodder Beitrag anzeigen
          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.
          PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

          Kommentar


          • #6
            Zitat von VPh Beitrag anzeigen
            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.

            Kommentar


            • #7
              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

              Kommentar


              • #8
                Zitat von Gamemodder Beitrag anzeigen
                P.S. Explain ergibt dieses Ergebnis:
                temporary und filesort. Dort findest Du Deine Zeit.
                PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                Kommentar


                • #9
                  Zitat von Gamemodder Beitrag anzeigen
                  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()).

                  PHP-Code:
                  SELECT bznCOUNT(A.bzn) AS cnt FROM `fe_usersU
                  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.

                  Zitat von hausl Beitrag anzeigen
                  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.

                  Kommentar


                  • #10
                    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
                    Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
                    PHP.de Wissenssammlung | Kein Support per PN

                    Kommentar


                    • #11
                      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.

                      Kommentar


                      • #12
                        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
                        Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
                        PHP.de Wissenssammlung | Kein Support per PN

                        Kommentar


                        • #13
                          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).
                          Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

                          Kommentar


                          • #14
                            Zitat von Gamemodder Beitrag anzeigen

                            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:

                            Code:
                            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:

                            Code:
                            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:

                            Code:
                            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.
                            PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                            Kommentar


                            • #15
                              Über wie viele Datensätze reden wir?
                              Im worst case: 27 * 1 * 27 *1209 * 1230 * 14590 * 29

                              Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

                              Kommentar

                              Lädt...
                              X