MySql Abfrage mit mehreren Subselect optimieren

[Sprint]

Hallo zusammen,

ich habe hier eine recht umfangreiche Abfrage mit mehreren Subselects, die zwar funktioniert, aber pro Datensatz etwa eine Viertelsekunde braucht. Und bei 40-50 Abfragen summiert sich das ganz schön.

if ($np == 1){
    $np1 = $bubfelder;
    $np2 = 'n.npexpress, n.zeit as nachzeit, n.h48,';
    $np3 = ', nachprf n';
    $np4 = ' and p.pid = n.pruefnr and n.erledigt = 0';
    $np5 = "and n.lfnr = '$lfnr'";
}

$sql = "
SELECT p.pid        AS suchpid,
       k.firmenname AS kdname,
       p.zeit,
       vk.nachname,
       vk.vorname,
       vk.vknr,
       p.firmenname AS prueffirmenname,
       p.kdnr,
       p.steuernr,
       p.express,
       p.status,
       $np1
       substr(p.usernr,1,5) AS dkundenr,
       p.kdnr                    AS eukundenr,
       p.gruen1,
       $np2
       (
                SELECT   date_format(zeit,'%d.%m.%Y')
                FROM     pruefung p
                WHERE    p.kdnr = eukundenr
                AND      substr(p.usernr,1,5) = dkundenr
                ORDER BY zeit DESC
                LIMIT    1) AS datum,
       (
                SELECT   u.kuerzel
                FROM     user u,
                         pruefprot pp
                WHERE    u.user = pp.usernr
                AND      pp.pruefnr = suchpid
                ORDER BY pp.zeit DESC
                LIMIT    1) AS lastuser,
       (
              SELECT max(p.pid)
              FROM   pruefung p
              WHERE  substr(p.pid,1,5) = '$kdnr') AS maxpid,
       (
              SELECT k.firmenname
              FROM   kunden k
              WHERE  k.kdnr = eukundenr) AS firmenname,
       (
                SELECT   s.sendezeit
                FROM     sendeliste s
                WHERE    s.pid = p.pid
                AND      erledigt = '0000-00-00 00:00:00'
                ORDER BY s.slnr DESC
                LIMIT    1) AS sendezeit
FROM   pruefung p,
       pruefvk vk,
       kunden k 
       $np3
WHERE  p.pid = '$pid'
AND    k.kdnr = substr(usernr,1,5)
AND    p.usernr = vk.vknr $np4 $np5";

Kann das zeitlich noch optimiert werden oder müssen wir damit leben?

 

[Yaslaw]

Kannst du mal in den Subselects die Tabellenaliase mitgeben, damit der DB-Struktur-Unkundige (also im Forum alle ausser dir) sieht, auf welche Tabelle sich die Bedinungen beziehen.

Was du sicher machen kannst, ist gezielt Indexe zu setzen. Dann versuchen, die Subselects aus dem From-Teil in den Where Teil zu verschieben. Ich kann dir da noch nicht helfen, da ich wie oben erwähnt, nicht sehe welche Felder woher stammen.

Warum machst du 40 bis 50 Abfragen einzeln? Frage alle auf einmal ab.

 

[Sprint]

Ich habe die Aliase ergänzt.

Zwei der Subselects lassen sich wohl zu einem JOIN umbauen, die anderen lassen sich nicht direkt mit den auszugebenden Datensätzen verbinden, werden aber als zusätzliche Information benötigt.
Mit JOINs stehe ich aber immer noch etwas auf dem Kriegsfuß. Bei zwei Tabellen bekomme ich das noch hin, wenn dann aber wie hier vier oder fünf ins Spiel kommen, blicke ich das überhaupt nicht mehr durch.

Die Daten, die in die Abfrage eingesetzt werden und nach denen es sich auch richtet, ob die zusätzlichen $np... Variablen benötigt werden, werden am Anfang aus verschiedenen Quellen zusammengesammelt, die zum Teil nur wenig miteinander zu tun haben und auch selbst wiederum von zusätzlichen Filtern abhängen.

 

[Yaslaw]

Du brauchst in einigen Fällen das Maximum aus einer Gruppe. Dazu gibts in MySQL den over partition BY. (MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax)
In den Subselects im FROM wo es geht mit Distinct arbeiten, um Mehrfachresultate zu vermeiden

Dann könnte das ganze etwa so aussehen (hat sicher noch Flüchtigkeitefehler drin

select
    ...
    -- Die höchste Zeit pro dkundenr
    max(p.zeit) over (partition by p.dkundenr) as datum,
    -- Den ersten Inhalt des Feldes kuerzel pro pruefnr sortiert nach zeit
    FIRST_VALUE(pp.kuerzel) over (partition BY pp.pruefnr ORDER BY p.zeit desc) as lastuser,
    -- Grösste pid, falls die kdnr mit der PHP-Variable übereinstimmt.
    max(case when p.kdnr = '$kdnr' then p.pid else null end) over() as maxpid,
    k1.firmenname AS kdname,  
    k2.firmenname,
    s.sendezeit
FROM
    (
        select *,
            substr(p.usernr,1,5) as dkundenr,
            substr(p.pid,1,5) as kdnr
        from pruefung
    ) p
    LEFT JOIN (
        select disitnct
            pp_1.pruefnr,
            u_1.kuerzel,
        from
            user u_1,
            pruefprot pp_1
        where u_1.user = pp_1.usernr
    ) pp ON pp.pruefnr = p.pid
    LEFT JOIN (
        select distinct kdnr, firmenname
        from kunden
    ) k1 ON k1.kdnr = p.dkundenr
    LEFT JOIN (
        select distinct kdnr, firmenname
        from kunden
    ) k2 ON k2.kdnr = p.kdnr
    LEFT JOIN (
        select distinct
            pid,
            -- Erste Sendezeit pro pid sortiert nach slnr
            FIRST_VALUE(sendezeit) over (partition by pid order by slnr desc) as sendezeit
        from sendeliste
        where erledigt = '0000-00-00 00:00:00'
    ) s ON s.pid = p.pid
    LEFT JOIN pruefvk vk on p.usernr = vk.vknr
WHERE
    p.pid = '$pid'

 

[Sprint]

Ich habe jetzt die letzten Stunden mit dem Versuch verbracht, das ganze zu verstehen - keine Chance. Dann habe ich die Variablen ausgefüllt und mal bei PhpMyadmin reingeworfen. Ich hatte noch nie 56 Fehler auf ein Mal. Dabei scheint es auch so zu sein, daß er keine Aliase bei over verträgt.

 

[Yaslaw]

Mein lokales phpmyadmin schluckt aliase. Und zeig doch mal dein Test-SQL?
Grundsätzlich solltet du dich rantasten. Wenn es nicht klappt, mal Schritt für Schritt testen. Mal ohne die over-Felder, nur die Joins. Dann ein Feld nach dem anderen dazu.

 

[Sprint]

Bei den Joins fängt mein Verständnisproblem schon an.

FROM
    (
        select *,
            substr(p.usernr,1,5) as dkundenr,
            substr(p.pid,1,5) as kdnr
        from pruefung
    ) p
    LEFT JOIN (
        select disitnct
            pp_1.pruefnr,
            u_1.kuerzel,
        from
            user u_1,
            pruefprot pp_1
        where u_1.user = pp_1.usernr
    ) pp ON pp.pruefnr = p.pid

Das sieht für mich so aus, als wenn du Rückgaben aus Selects als Tabellennamen verwenden würdest.

Obendrein befürchte ich, daß auch wenn das irgendwann zum Laufen kommt, ich bei späteren Änderungen wieder genauso dumm dastehe und nicht weiß, wie ich die Änderungen einbaue.

Wäre es da nicht besser, das komplette Konstrukt incl. aller vorhergehenden und damit zusammenhängenden Aktionen neu zu bauen und da mehr zusammenzufassen?

 

[Yaslaw]

Jepp, das sind Subselects, die als neue Quelle deinen. Der Alias p beinhaltet das Subselect. Ist sicher performanter als ein Subselect im SELECT-Teil.
Wenn du so komplexe sachen bauen willst, kommst du nicht um die JOINs herum. Zeit diese zu lernen.

Wäre es da nicht besser, das komplette Konstrukt incl. aller vorhergehenden und damit zusammenhängenden Aktionen neu zu bauen und da mehr zusammenzufassen?

Kann sein, ich kenne die DB nicht und auch bei deinem Select habe ich keine Ahnung was dahinter steckt.

 

[Sprint]

Joins sind prinzipiell nicht das Problem. Nur bei so komplexen Bauten wie das hier werden würde, steige ich aus. Zumal diese Abfrage die einzige so umfangreiche in dem kompletten Projekt ist.
Und da der Grundstock für diese Abfrage schon über zehn Jahre alt ist und immer wieder was dazu gebaut wurde, dürfte es wohl besser sein, ich baue das neu auf.

Trotzdem danke für deine Hilfe. Hat mir auch meine Entscheidung erleichtert.