Hallo,

ich stelle ein Anfrage mit PDO und verwende SMARTY und das Paginator plugin.

Die Anfrage geht über mehre Tabellen und das alles funktioniert auch super. Nur kann es vor kommen das zum Beispiel 100.000 Datensätze ausgegeben werden und dann braucht der ne ganze weile bevor das fertig ist mit cachen usw.

Da ich den Paginator benutze stelle ich 2 Anfragen. Eine zum ermitteln der Anzahl von Datensätzen und dann die Ausgaben.

Wie kann ich das Optimieren? Also so das nicht bei 100.000 Einträgen das Ding so rattert. Ich brauch aber ein Zugriff auf alle Datensätze.



Grüße

Um ein SQL zu optimieren ist es Sinnvoll das SQL zu sehen.
Ansonsten gibt es ganze Bücher über das Thema Performance-Optimierung. Das kann man dir hier nicht einfach so in 2 Sätzen allgemein beantworten

So hier mal mein SQL:

PHP-Code:



$qry = $db->prepare("
            SELECT SQL_CALC_FOUND_ROWS DISTINCT
                r.id
                
            FROM
                anfragen r
            LEFT JOIN
                transa t
            ON
                r.id = t.a_id
            LEFT JOIN
                frontend u
            ON
                r.user_id = u.id
            LEFT JOIN
                anbieter a
            ON
                a.id=r.anbieter_id
            LEFT JOIN
                (SELECT * FROM status WHERE tid='44') as s
            ON
                s.tranid = t.id
            
            ".$where."
            ORDER BY
                r.datum DESC
        ");
        $qry->execute();        
        SmartyPaginate::setTotal($qry->rowCount());
        
        $qry = NULL;
        
        $qry = $db->prepare("
            SELECT DISTINCT
                r.*,
                t.*,
                a.*,
                u.*
                
                
            FROM
                anfragen as r
            LEFT JOIN
                transa as t
            ON
                r.id = t.a_id
            LEFT JOIN
                frontend as u
            ON
                r.user_id = u.id
            LEFT JOIN
                anb as a
            ON
                a.id=r.anb_id
            LEFT JOIN
                (SELECT * FROM status WHERE tid='44') as s
            ON
                s.transaid = t.id

            ".$where."
            ORDER BY
                r.datum DESC
            LIMIT
                ".SmartyPaginate::getCurrentIndex().",".SmartyPaginate::getLimit()."
        ");
        $qry->execute();
        $requests = $qry->fetchAll(); 


der erste um die Anzahl der Einträge zu ermitteln. Pagination läuft über das Smarty->plugin

schön wäre es vielleicht die Anzahl der Datensätze gleich zu ermitteln, SQL_CALC_FOUND_ROWS nur muss da noch was passieren.


Die Einträge aus Tabelle Status können ja mehre sein, die ordne ich später mit einer Schleife und trage jeden Datensatz die Einträge aus Status in einem Feld zu, getrennt durch Komma.

Vielleicht ist das auch ein unnötiger Schritt. Schön wäre es ja alle Einträge aus Status dem Datensatz schon beim SQL hinzu zuordnen, doch habe ich dazu leider keine Idee bzw. sind mir nicht alle Möglichkeiten von MySQL bewusst.

Um die Anzahl zu ermitteln schon mal 2 Punkte:
1) Der ORDER BY verbraucht viel Zeit und ist für die Anzahl total irelevant (= Performance-Fresser).
2) Für die Anzahl sind nur die LEFT JOINS notwednig, bei denen mehrere Eintrage pro anfrage vorhanden sind oder die im WHERE verwednet werden.
zB Anbieter: Ich denke mal, da gibts pro Antrag ein oder kein Anbieter. Aber nicht mehrere. Ergo ist dieser LEFT JOIN für den Count irrelevant ausser der WHERE verweisst auf die Tabelle

Zu deiner eigentlichen Abfrage
1) Nimm nicht alle Felder aller Tabellen. Reduziere den SELECT-Bereich auf dei Felder die du auch wirklich brauchst.
2) die Staties willst du mit Kommas getrennt anzeigen? Dann mach das gleich im SQL. Mach ein GROUP BY und füge die Staties mittels GROUP_CONCAT zusammen. Das reduziert die Anzahl Zeilen und du kannst dir eine komplizierte PHP-Logik sparen

Code sql:

1 2 3 4 5 6 7 8 9 10 11 12 13

... LEFT JOIN (     SELECT         transaid,         GROUP_CONCAT(statusname ORDER BY statusorder SEPARATOR ', ') AS states     FROM         `status`     WHERE tid='44'     GROUP BY         transaid     ) AS s       ON s.transaid = t.id ...

Kann ich mir mit dem SQL_CALC_FOUND_ROWS die anfrage für dei Anzahl sparren?

achso und anbieter sind mehrere, wenn ich eine suche ohne where anfrage muss er mir ja alles zurückgeben, dei anderen anfragen, wenn ich ja nur nach anbieter oder und suche, gibt mir ja die anzahl der such anfrage zurück...

ps.: ich nehme nicht alle felder, habe das nur gekürzt für den beitrag hier

Zitat von Strafi

Kann ich mir mit dem SQL_CALC_FOUND_ROWS die anfrage für dei Anzahl sparren?

Nein. Denn die Tabelle `anfrage`ist die Zentrale Datentabelle in diesem Query. Die brauchst du immer.

Zitat von Strafi

achso und anbieter sind mehrere, wenn ich eine suche ohne where anfrage muss er mir ja alles zurückgeben, dei anderen anfragen, wenn ich ja nur nach anbieter oder und suche, gibt mir ja die anzahl der such anfrage zurück...

Gut, das wusste ich mangels Informationen nicht.
Aber wenn du die Staties nachher eh kommasepariert haben willst, kannst du sie getrost aus der Zähl-Abfrage entfernen. Denn diese ergebn mit meinem GROUP_CONCAT() nur noch eine Zeile.

Hat es was genützt den ORDER BY aus der Zähl-Abfrage zu entfernen?
Hat es was genützt die Staites mit dem GROUP_CONCAT zusammenzufassen um dei Anzahl Zeilen zu verringern?

Wie hat sich der Explain-Plan verändert? Wenn du nicht weisst was das ist, dann google mal. Der ist sehr Zentral beim verbssern der Performance von SQL-Statements.

Code :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

$qry = $db->prepare("             SELECT DISTINCT                 r.datum,                 r.id,                 r.user_id,                 r.name,                 r.vorname,                 r.geschlecht,                 r.geburts_tag,                 r.geburts_monat,                 r.geburts_jahr,                 r.strasse,                 r.hnr,                 r.hb,                 r.hz,                 r.plz,                 r.ort,                 r.verwendungszweck,                 a.name,                 t.timeout,                 t.preis,                 t.preis as vk,                 t.id as transaction_id,                 u.login                                             FROM                 anfragen as r             LEFT JOIN                 transaktionen as t             ON                 r.id = t.a_id             LEFT JOIN                 frontend_user as u             ON                 r.user_id = u.id             LEFT JOIN                 anbieter as a             ON                 a.id=r.anbieter_id             LEFT JOIN (                 SELECT                     s.id,                     GROUP_CONCAT(s.id SEPARATOR ', ') AS x_44es                 FROM                     stati                 WHERE                     tableid='44'                 GROUP BY                     s.id                 )                 AS                     s                   ON                     s.transactionid = t.id               ".$where."             ORDER BY                 r.datum DESC             LIMIT                 ".SmartyPaginate::getCurrentIndex().",".SmartyPaginate::getLimit()."         ");

Funktioniert nicht... habe bestimmt ein Fehler.... an der falschen Stelle?

jepp. Im subquery gruppierst du nach id und gibsts auch nur das Feld is weiter. Aber im LEFT JOIN prüfst du auf transactionid

Am besten testet man die SQLs mittels phpMyAdmin oder so. Siehe hierzu auch das Tutorial [PHP] [MySQL] Debug Queries

Okay ich aheb jetzt eine ausgabe, auf die ich fast 2 minuten gewartet habe. Vielleicht stimmt auch was nicht mit dem server.


habe es jetzt so:

Code sql:

1 2 3 4 5 6 7 8

LEFT JOIN (   SELECT transactionid, GROUP_CONCAT( id SEPARATOR ', ' ) AS x_44es FROM stati WHERE tableid = '44' GROUP BY transactionid ) AS s ON s.transactionid = t.id

und habe auch den kompletten Query im PHPAdmin geprüft.
Das Feld x_44es gibt es nicht.

Zitat von Strafi

Das Feld x_44es gibt es nicht.

Wo gibt es das Nicht?
Hast du das Subquery auch mal alleine gestestet?

Nun zu deinen Performance-Problem:
Was sagt EXPLAIN?
Hast du Sinnvolle Indexe?
Kannst du deine Indexe so anlegen, dass gff FillIndexScans anstelle von FullTableScans durchgeführt werden können?

So und nun mein Abschlusswort zu diesem Thread.
Performance-Verbesserungen sind keine eindeutige und einfache Sache. Es ist ein testen und heran tasten. Auswerten von Explainplans, SQL umschreiben - ggf. total umschreiben, weiter testen, Anzahl Datensätze der Tabelle vergleichen, ggf. Subqueries machen um die Datenmenge so früh wie möglich klein zu kriegen etc.
Also, so einfach *schnipp*und ein SQL ist schnell - das kannst du vergessen. Diese ganzen Punkte die ich aufgezählt habe gehen auch nicht einfach so mit im Forum hin und her schreiben. Lies dich in das Thema ein. Verscuh es zu verstehen. Und dann setz dich hin und probier dich durch.

Ja hab dich schon verstanden und schau mir natürlich die Sachen auch an. aber es liegt anscheint an etwas anderem.

die Ausgabe brauch 1-2 minuten auch im PHPAdmin aber PHPAdmin sagt:

Zeige Datensätze 0 - 29 (100,216 insgesamt, die Abfrage dauerte 0.0227 sek.)

ist doch eigentlich okay****?

es muss also was anderes sein, ich weiß nur, ist die anfrage im chache geht es alles flott.

Das heisst, dass er für die ersten 30 Datensätze 0.0227 Sekunden brauchte

Nur grob und stimmt natürlich nicht ganz mit der Realität überein
0.0227*100'0000/30 = ~76 Sekunden

mmm.. ich dachte das ist für alle.. naja okay.. aber das ist ne ganz normal abfrage und nicht gerade die welt.

Hier nochmal der EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY r ALL NULL NULL NULL NULL 100216 Using temporary
1 PRIMARY t ref a_id a_id 4 my_db.r.id 1
1 PRIMARY u eq_ref PRIMARY PRIMARY 4 my_db.r.user_id 1
1 PRIMARY a eq_ref PRIMARY PRIMARY 1 my_db.r.anbieter_id 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Distinct
2 DERIVED stati ALL tableid NULL NULL NULL