SQL String optimieren - Nachrichtensystem

[martinm79]

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.

// 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

        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 =0 

Aber wie kann man das noch optimieren?

[Manko10]

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.

[martinm79]

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?

[martinm79]

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.

            ON (b.user_id_ein='".escape($user->user_id)."' 
                OR b.user_id_aus='".escape($user->user_id)."') 
            AND b.del_ein =0 

Ich möchte die Anzahl der Nachrichten im Posteingang und im Postausgang zählen.
Das liegt wohl am OR was die Zeit frist.

[H2O]

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

[martinm79]

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

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?

[H2O]

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.

[martinm79]

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

$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.

[tontechniker]

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?

[martinm79]

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.

[tontechniker]

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.

[martinm79]

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.

[H2O]

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.

[tontechniker]

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.

---

[martinm79]

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

$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.