SQL String optimieren - Nachrichtensystem

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

  • SQL String optimieren - Nachrichtensystem

    Hallo an alle.

    Vielleicht kann mir hier ja jemand ein Tipp geben.
    Ich habe ein SQL String von meinem Nachrichtensystem. Dieser brauchte zuerst 6 Sekunden. Auf 2.5 Sekunden habe ich ihn schon bekommen, aber ein kleiner Fehler ist da immer noch drinne.

    Der SQL String soll mir die ID vom Absender ermitteln.
    Eine Antwort ID für den Naachrichtenverlauf.
    Das Datum der Nachricht.
    Ob Nachricht gelesen oder ungelesen wurde.
    Die Nachricht.
    Die Anzahl der Nachrichten vom Empfänger.
    Die Anzahl der Nachrichten vom Sender.
    Ob der Empfänger gelöscht wurde.
    Ob der Empfänger einen geblockt hat.
    Ob man selber den Empfänger blockt.

    PHP-Code:
    // Das war der erste orginale SQL String.
    // 6 sekunden
    $sql "SELECT
                a.user_id_aus,
                a.antwort_id,
                DATE_FORMAT(a.datum_gesendet,'%d.%m.%y %H:%i') datum_gesendet,
                IF(a.datum_gelesen,0,1) ungelesen,
                a.nachricht,
                COUNT(DISTINCT(b.nachrichten_id)) a,
                COUNT(DISTINCT(c.nachrichten_id)) b,
                d.del_user_id,
                COUNT(e.sperre_id) gesperrt,
                COUNT(f.sperre_id) ist_gesperrt
            FROM "
    .NACHRICHTEN." a
            LEFT JOIN "
    .NACHRICHTEN." b
                ON (b.user_id_ein='"
    .escape($user->user_id)."' 
                    OR b.user_id_aus='"
    .escape($user->user_id)."') 
                AND b.del_ein =0
            LEFT JOIN "
    .NACHRICHTEN." c
                ON (c.user_id_ein=a.user_id_aus 
                    OR c.user_id_aus=a.user_id_aus) 
                AND c.del_aus =0
            LEFT JOIN "
    .USER." d ON d.user_id=a.user_id_aus
            LEFT JOIN "
    .SPERRE." e
                ON e.user_id = a.user_id_aus
                AND e.user_id_gesperrt = '"
    .escape($user->user_id)."'
                AND e.del = 0
            LEFT JOIN "
    .SPERRE." f
                ON f.user_id = '"
    .escape($user->user_id)."'
                AND f.user_id_gesperrt = a.user_id_aus
                AND f.del = 0
            WHERE a.nachrichten_id='"
    .escape($_GET['_id'])."'
            AND a.user_id_ein='"
    .escape($user->user_id)."'
            AND a.del_ein =0
            GROUP BY a.nachrichten_id
            LIMIT 1;"
    ;

    // Nach der Änderung von LEFT JOIN in INNER JOIN
    // Die Änderung habe ich mit [COLOR=green]grün[/COLOR] gekennzeichnet
    // 2.5 Sekunden
    $sql "SELECT
                a.user_id_aus,
                a.antwort_id,
                DATE_FORMAT(a.datum_gesendet,'%d.%m.%y %H:%i') datum_gesendet,
                IF(a.datum_gelesen,0,1) ungelesen,
                a.nachricht,
                COUNT(DISTINCT(b.nachrichten_id)) a,
                COUNT(DISTINCT(c.nachrichten_id)) b,
                d.del_user_id,
                COUNT(e.sperre_id) gesperrt,
                COUNT(f.sperre_id) ist_gesperrt
            FROM "
    .NACHRICHTEN." a
            [COLOR=green]INNER JOIN[/COLOR] "
    .NACHRICHTEN." b
                ON (b.user_id_ein='"
    .escape($user->user_id)."' 
                    OR b.user_id_aus='"
    .escape($user->user_id)."') 
                AND b.del_ein =0
            [COLOR=green]INNER JOIN[/COLOR] "
    .NACHRICHTEN." c
                ON (c.user_id_ein=a.user_id_aus 
                    OR c.user_id_aus=a.user_id_aus) 
                AND c.del_aus =0
            LEFT JOIN "
    .USER." d ON d.user_id=a.user_id_aus
            LEFT JOIN "
    .SPERRE." e
                ON e.user_id = a.user_id_aus
                AND e.user_id_gesperrt = '"
    .escape($user->user_id)."'
                AND e.del = 0
            LEFT JOIN "
    .SPERRE." f
                ON f.user_id = '"
    .escape($user->user_id)."'
                AND f.user_id_gesperrt = a.user_id_aus
                AND f.del = 0
            WHERE a.nachrichten_id='"
    .escape($_GET['_id'])."'
            AND a.user_id_ein='"
    .escape($user->user_id)."'
            AND a.del_ein =0
            GROUP BY a.nachrichten_id
            LIMIT 1;"
    ;

    // Sobald ich die eine Tabelle weglasse, 
    // die die Anzahl der Gesamtnachrichten vom Nachrichtensender ermittelt
    // geht die Zeit runter.
    // Die Änderung habe ich mit [COLOR=green]grün[/COLOR] gekennzeichnet
    // 0.0068 Sekunden
    $sql "SELECT
                a.user_id_aus,
                a.antwort_id,
                DATE_FORMAT(a.datum_gesendet,'%d.%m.%y %H:%i') datum_gesendet,
                IF(a.datum_gelesen,0,1) ungelesen,
                a.nachricht,
                COUNT(DISTINCT(b.nachrichten_id)) a,

                d.del_user_id,
                COUNT(e.sperre_id) gesperrt,
                COUNT(f.sperre_id) ist_gesperrt
            FROM "
    .NACHRICHTEN." a
            INNER JOIN "
    .NACHRICHTEN." b
                ON (b.user_id_ein='"
    .escape($user->user_id)."' 
                    OR b.user_id_aus='"
    .escape($user->user_id)."') 
                AND b.del_ein =0
            
            [COLOR=green] hier ein INNER JOIN weggenommen [/COLOR]
            
            LEFT JOIN "
    .USER." d ON d.user_id=a.user_id_aus
            LEFT JOIN "
    .SPERRE." e
                ON e.user_id = a.user_id_aus
                AND e.user_id_gesperrt = '"
    .escape($user->user_id)."'
                AND e.del = 0
            LEFT JOIN "
    .SPERRE." f
                ON f.user_id = '"
    .escape($user->user_id)."'
                AND f.user_id_gesperrt = a.user_id_aus
                AND f.del = 0
            WHERE a.nachrichten_id='"
    .escape($_GET['_id'])."'
            AND a.user_id_ein='"
    .escape($user->user_id)."'
            AND a.del_ein =0
            GROUP BY a.nachrichten_id
            LIMIT 1;"

    Also das Problem liegt wohl beim
    PHP-Code:
            INNER JOIN ".NACHRICHTEN." c
                ON 
    (c.user_id_ein=a.user_id_aus 
                    
    OR c.user_id_aus=a.user_id_aus
                AND 
    c.del_aus =
    Aber wie kann man das noch optimieren?
    Gut geraten ist halb gewußt.

  • #2
    LEFT JOIN ist schneller als INNER JOIN.
    Außerdem könntest du mit EXPLAIN herausfinden, ob du auch alle möglichen Indizes benutzt.
    Eventuell solltest du auch Redundanzen in Kauf nehmen. Strikte Normalisierung ist zwar gut, aber manchmal muss man einen Kompromiss eingehen.

    Kommentar


    • #3
      Eigentlich benutze ich auch immer LEFT JOIN.
      Das mit INNER habe ich nur getestet und da war es schneller.

      Das komische ist auch, das das alles offline auf meinem localhost schnell läuft. Nur online auf der Seite dauert es so lange.
      Aber das kann ja auch an der Nachrichtenanzahl liegen, weil ich offline nicht so viele habe.

      Das mit EXPLAIN habe ich schon geguckt. Aber ich verstehe nicht wie ich da die INDIZE da einbauen sollte.

      Also meinst du, das ich den großen SQL String mal aufteilen sollte?

      Oder ein UNION probieren?
      Gut geraten ist halb gewußt.

      Kommentar


      • #4
        Also EXPLAIN spuckt mir das aus.

        id select_type table type possible_keys key key_len ref rows Extra
        1 SIMPLE a const PRIMARY,com_nachrichten_FKIndex1,com_nachrichten_F... PRIMARY 4 const 1
        1 SIMPLE b ALL com_nachrichten_FKIndex1,com_nachrichten_FKIndex2 NULL NULL NULL 1205 Using where
        1 SIMPLE c ALL com_nachrichten_FKIndex1,com_nachrichten_FKIndex2 NULL NULL NULL 1205 Using where
        1 SIMPLE d eq_ref PRIMARY PRIMARY 4 const 1
        1 SIMPLE e ref com_sperre_FKIndex1,com_sperre_FKIndex2 com_sperre_FKIndex2 5 const 1 Using where
        1 SIMPLE f ref com_sperre_FKIndex1,com_sperre_FKIndex2 com_sperre_FKIndex1 5 const 1 Using where

        Ok, das ALL muß weg, so wie ich das verstehe, aber wie soll ich da noch machen?
        Er hält sich wohl hier auf.
        PHP-Code:
                    ON (b.user_id_ein='".escape($user->user_id)."' 
                        
        OR b.user_id_aus='".escape($user->user_id)."'
                    AND 
        b.del_ein =
        Ich möchte die Anzahl der Nachrichten im Posteingang und im Postausgang zählen.
        Das liegt wohl am OR was die Zeit frist.
        Gut geraten ist halb gewußt.

        Kommentar


        • #5
          Ich mag mich jetzt nicht im Detail mit dieser Abfrage befassen, da sie sowieso nicht sehr sinnvoll ist. Wenn du Aggregats-Funktionen und GROUP BY benutzst, so müssen alle Attribute, die abgefragt werden, auch in der GROUP BY-Klausel stehen. Für alle anderen (ausser den direkt 1:1 vom gruppierten Attribut abhängigen) Attribute erhälst du Zufallsresultate. MySQL ist hier eine Ausnahme, die meisten andern RDBMS erlauben solche Abfragen gar nicht. Vermutlich müsstest du mit Subselects arbeiten. Da ich aber die Struktur deiner Tabellen nicht kenne, kann ich dir auch nichts genaueres sagen.
          Ausserdem rufst du sechs mal escape($user->userid) innerhalb der Abfrage auf. Ich weiss zwar nicht, was diese Funktion macht, aber vermutlich wäre es schnelle, das Resultat in eine Varaible zu schreiben, und dann diese zu benutzen
          Gruss
          H2O

          Kommentar


          • #6
            Ok, danke das bringt mich schon ein bisschen weiter.
            Da ich mir SQL auch nur Freizeitmäßig beigebracht habe, bin ich natürlich
            immer dankbar wenn ich dazu lerne.

            Also zu der Funktion
            PHP-Code:
            function escape($s=false){
                return 
            trim(mysql_real_escape_string($s));

            Und sobald ich eine Aggregatfunktion benutze, muß ich das GROUP BY auf jede Tabelle anwenden? Ok, werd ich machen.

            Also ich werde die SQL Abfrage nochmal nachbauen.

            Was mich nochmal interessiert.
            Ist es egal ob ich:
            LEFT JOIN ".USER." d ON d.user_id = a.user_id_aus
            oder
            LEFT JOIN ".USER." d ON a.user_id_aus = d.user_id
            schreibe?
            Wenn nein, welche Variante ist besser?
            Gut geraten ist halb gewußt.

            Kommentar


            • #7
              Original geschrieben von martinm79

              Und sobald ich eine Aggregatfunktion benutze, muß ich das GROUP BY auf jede Tabelle anwenden?
              Nein, sondern auf jedes in der Abfrage vorhandene, nicht aggregierte Attribut.
              Aber vielleicht sagst du uns mal tabellarisch, was due eigentlich erreichen willst, und wie deine drei Tabellen aussehen. Möglicherweise gibt es auch dort Verbesserungsmöglichkeiten.
              Des weiteren ist mir auch unklar, was $user für ein Objekt ist.
              Gruss
              H2O

              Kommentar


              • #8
                Ok, mitlerweile glaube ich zu wissen an was es genau liegt, das die
                Abfrage so lange dauert. Ich habe es mal auf das wesentliche beschränkt.

                Habe die Tabelle mal verkürzt und nur die wesentlichen Spalten aufgeführt.
                user_id ist immer die User ID vom User.
                "del_ein" und "del_aus" ist eine Tinyint 1 mit 0 oder 1
                Der Rest dürfte selbterklärend sein.

                User Tabelle
                user_id | nick

                Nachrichten Tabelle
                nachrichten_id | user_id_ein | user_id_aus | del_ein | del_aus

                PHP-Code:
                $meine_user_id 1;
                $empfaenger_user_id 2;

                $sql "SELECT
                            COUNT(DISTINCT(a.nachrichten_id)) anzahl_msg_empfaenger,
                            COUNT(DISTINCT(b.nachrichten_id)) anzahl_msg_meine
                        FROM "
                .NACHRICHTEN." a
                        LEFT JOIN "
                .NACHRICHTEN." b
                            ON    (b.user_id_ein='"
                .$meine_user_id."' AND b.del_ein = 0)
                            OR    (b.user_id_aus='"
                .$meine_user_id."' AND b.del_aus = 0)
                        WHERE
                            (a.user_id_ein='"
                .$empfaenger_user_id."' AND a.del_ein = 0)
                        OR    (a.user_id_aus='"
                .$empfaenger_user_id."' AND a.del_aus = 0)
                        ;"

                Durch den LEFT JOIN geht die Zeit enorm noch oben. Sobald ich ihn raus nehme ist die Zeit aktzeptabel.

                Sinn und Zweck ist es, jeweils die Anzahl der Nachrichten vom Empfänger und vom Nachrichtensender zu ermitteln.
                Gut geraten ist halb gewußt.

                Kommentar


                • #9
                  Sinn und Zweck ist es, jeweils die Anzahl der Nachrichten vom Empfänger und vom Nachrichtensender zu ermitteln.
                  Versteh ich immer noch nicht. Was macht del_ein und del_aus?
                  Die Regeln | rtfm | register_globals | strings | SQL-Injections | [COLOR=silver][[/COLOR][COLOR=royalblue]–[/COLOR][COLOR=silver]][/COLOR]

                  Kommentar


                  • #10
                    del_ein ist für den Nachrichteneingang und steht auf 0, wenn der Nachrichtenempfänger die Nachricht nicht gelöscht hat und auf 1 wenn sie gelöscht wurde.

                    Und del_aus ist für den Nachrichtenausgang. Es steht auf 0 wenn er die Nachricht noch nicht gelöscht hat und auf 1 wenn er sie gelöscht hat.
                    Gut geraten ist halb gewußt.

                    Kommentar


                    • #11
                      Soviel kann man sich noch zusammenreimen - aber wofür ist das gut?
                      Sinn und Zweck ist es, jeweils die Anzahl der Nachrichten vom Empfänger und vom Nachrichtensender zu ermitteln.
                      Versteh ich auch noch nicht - erklär mal das Konzept der Tabelle.
                      Die Regeln | rtfm | register_globals | strings | SQL-Injections | [COLOR=silver][[/COLOR][COLOR=royalblue]–[/COLOR][COLOR=silver]][/COLOR]

                      Kommentar


                      • #12
                        Weil jeder User nur eine bestimmte Anzahl an Nachrichten verschicken darf.
                        Und wenn ein Posteingang von jemanden voll ist, so darf ihm kein anderer mehr schreiben und er darf auch keine Nachrichten mehr verschicken.

                        Deswegen prüfe ich das vorher, nach der Nachrichtenanzahl.
                        Gut geraten ist halb gewußt.

                        Kommentar


                        • #13
                          Original geschrieben von martinm79
                          Durch den LEFT JOIN geht die Zeit enorm noch oben. Sobald ich ihn raus nehme ist die Zeit aktzeptabel.
                          Du hast ja auch keinen LEFT JOIN, sondern - weil eine echte JOIN-Bedingung fehlt - einen CROSS JOIN. Das, was du als JOIN-Bedingung angibst, gehört in die WHERE-Klausel (ob sie dann Sinn macht, ist mir aber auch nicht ganz klar).
                          Ich bin mir nicht ganz sicher, ob ich dich richtig verstanden habe, aber ich glaube, ein JOIN hilft hier nicht weiter. Entweder du machst mehrere Abfragen, oder du versuchst es mal mit Subselects.
                          Gruss
                          H2O

                          Kommentar


                          • #14
                            Und wenn ein Posteingang von jemanden voll ist, so darf ihm kein anderer mehr schreiben und er darf auch keine Nachrichten mehr verschicken.
                            Ok, schöne Antwort. Wenn ich mir deine Abfrage so anschaue denke ich, dass es wesentlich sinnvoller ist (gerade im zusammenhang mit Aggeratfunktionen) zwei getrennte Abfrage, einmal für den User, einmal für den Empfänger zu machen.
                            OffTopic:
                            Ich habe im letzten Messagesystem solcher Art die Nachrichten jeweils zweimal vorgehalten, so können Sender und Empfänger sie unterschiedlich bearbeiten und man kann mit einem einfachen COUNT die Nachrichten eines Benutzers zählen.
                            Die Regeln | rtfm | register_globals | strings | SQL-Injections | [COLOR=silver][[/COLOR][COLOR=royalblue]–[/COLOR][COLOR=silver]][/COLOR]

                            Kommentar


                            • #15
                              Ok, jetzt habe ich es ja auch in einer 2. Abfrage ausgelagert.

                              PHP-Code:
                              $zeit->start("sql Anzahl");
                              $sql "SELECT COUNT(*) a
                                      FROM "
                              .NACHRICHTEN."
                                      WHERE
                                          (user_id_ein='"
                              .escape($user->user_id)."' AND del_ein = 0)
                                      OR    (user_id_aus='"
                              .escape($user->user_id)."' AND del_aus = 0);";
                              $r mysql_query($sql);
                              $a mysql_fetch_assoc($r);
                              $zeit->stop("sql Anzahl"); 
                              0.0023 Sekunden


                              Das ist das erste mal für mich, das ich eine Abfrage aufteile.

                              Aber danke für eure Hilfe.
                              Gut geraten ist halb gewußt.

                              Kommentar

                              Lädt...
                              X