Fabienne: Geschwindigkeit und Performance PHP - MySQL II

Hallo zusammen,

leider ist meinOriginal-Posting schon im Archiv.

Ich habe jetzt folgendes programmiert und getestet:

Folgende DBs gibt es:

customers:
id | name | ....
1  | Kohl | .....
2  | Müller | .....
3  | irgendwer | ....

kontakte:
id | customers_id | name | ansprechpartner_tourenplan
1  |       1      | Ich  | nein
2  |       1      | Du   | ja
3  |       2      | Toll | ja

turnover: (was: Welche Umsatz: _des_ Kunden oder _mit_ dem Kunde) id | customers_id | jahr | erstellt_am | wert | was
1  |      1       | 2007 | 2007-12-29  | 2890 | mit
2  |      1       | 2007 | 2007-12-30  | 3120 | mit
3  |      2       | 2008 | 2008-01-20  | 450  | mit
4  |      2       | 2008 | 2008-01-20  | 192020  | des

ZIEL:
alle Kundeninfos + der _eine_ Ansprechpartner für den Tourenplan, falls vorhanden + die Umsätze _mit_ dem Kunden, jeweils nur der letzte Eintrag, falls vorhanden ==> Ausgabe kommt später in eine CSV-Datei rein. Dort soll pro Zeile nur ein Kunde stehen mit den Ansprechpartnern und dem Umsatz.

Meine Lösung:

$anfrage="
SELECT
    customers.id,
    customers.*,
    kontakte.id,
    kontakte.*
FROM
    customers

-- Kontakte, die Ansprechpartner fuer Tourenplan sind (und zwar nur einer, falls mehrere vorhanden wären)
LEFT JOIN
  (
    SELECT k.* FROM customercontacts k WHERE k.ansprechpartner_tourenplan='ja' LIMIT 0,1
  ) kontakte
ON customers.id = kontakte.customer_id

-- Umsatz _mit_ Kunde aus Vorjahr, letzter Eintrag, nur ein Eintrag, falls mehrere vorhanden
LEFT JOIN
  (
    SELECT u.* FROM turnover u
          WHERE
              u.erstellt_am = (
                              SELECT MAX(um.erstellt_am) FROM turnover um
                              WHERE um.customer_id = u.customer_id
                                       AND um.jahr='2007' AND um.was='mit'
                              )
              AND u.was='mit'

) umsatz_vorjahr
ON customers.id = umsatz_vorjahr.customers_id

-- einfach mal etwas eingeschränkt, dass nicht gleich alle Datensätze rauskommen....
WHERE customers.id<20 LIMIT 0,5

";

Ergebnis:
MySQL-Server hat sich aufgehängt, CPU 100% Last!
Habe ich da irgendwo einen Zirkelbezug drin (Schleife??)?

Frage zu JOINs allgemein:
Ich bild zunächst eine Anfrage, die die bestehdnen Datensätze einschränkt (also eine Quasi-Neue-Tabelle suggeriert mit weniger Datensätzen), wobei die WHERE-Klausel erst ganz am Schluss ausgeführt wird (Tabelle wird also komplett in RAM geladen). Danach kann ich über JOINs beliebige andere Tabellen an die zuerst genannte Auswahl dranhängen (Korrelation über die Beziehung "ON erste.id=zweite.id").
Habe ich das mit den JOINs richtig verstanden?

Was ist bei JOINs mit Sub-Selects? Welche WHEREs darf ich da verwenden? Kann ich auf die Daten des Haupt-Selects überhaupt zugreifen oder macht genau das mir meinen Zirkelbezug = Endlosschleife?

Die Ergebnisse werden ja in einem Array geliefert.
Die id ist ja mehrfach vergeben. Wie komme ich an die einzelnen IDs?
Nur über z.B. $array[71] anstelle $array[id]?
Bei den einzeln definierten Vars geht das ja einfacher: $array[ansprechpartner_tourenplan]....
Gibt es da noch ne andere Möglichkeit außer der umständlichen Methode "... AS neuer_name"?

Vielen Dank für Eure Antworten!
Fabienne

  1. Halloa,

    Vielen Dank für Eure Antworten!

    Keine da, der mir weiterhelfen kann?
    Fabienne

  2. echo $begrüßung;

    MySQL-Server hat sich aufgehängt, CPU 100% Last!
    Habe ich da irgendwo einen Zirkelbezug drin (Schleife??)?

    In einem (wenn auch mit Subquerys gespickten) SELECT? Halte ich nicht für wahrscheinlich. Allerdings hast du vom PHP nur eine Variablenzuweisung gepostet. Vielleicht ist im Rest eine Schleife drin. Das bekommst du ja gut selbst raus, wenn du an strategisch wichtigen Punkten eine Kontrollausgabe einfügst. (Ein flush() hinterher bittet den Webserver die Ausgabe sofort an den Client zu senden, und nicht erst Ausgabepuffer zu füllen.) Du kannst auch ein Query-Log einschalten und dessen Füllung mitverfolgen (unter Unix: tail -f logfile)

    Ich bild zunächst eine Anfrage, die die bestehdnen Datensätze einschränkt [...], wobei die WHERE-Klausel erst ganz am Schluss ausgeführt wird [...]

    Die Reihenfolge der Abarbeitung der Klauseln ist nach logischen Gesichtspunkten geordnet und entspricht im wesentlichen der Reihenfolge ihrer Notation in einem SELECT-Statement (ungeachtet von irgendwelchen Optimierungen, die MySQL vornehmen mag). FROM und JOINs legen fest, welche Tabellen herangezogen werden und wie diese miteinander verknüpft werden. In den Join-Bedingungen werden die zu verwendenden Datensätze ausgewählt. Nun schränkt das WHERE die Auswahl weiter ein. GROUP BY fasst jetzt die Menge nach den Gruppierungsangaben zusammen. SELECT bestimmt nun die Spalten der Ergebnismenge, die mit HAVING noch weiter eingeschränkt und mit ORDER BY sortiert werden kann. Ein LIMIT geift sich abschließend daraus den als Ergebnismenge gewünschten Ausschnitt heraus. Subquerys werden an der Stelle ausgewertet, an der sie notiert wurden.

    Die Ergebnisse werden ja in einem Array geliefert.

    Das kommt darauf an, welche Fetch-Funktion du verwendest.

    Die id ist ja mehrfach vergeben. Wie komme ich an die einzelnen IDs?
    Nur über z.B. $array[71] anstelle $array[id]?

    Je nach Fetch-Funktion. Bei manchen anhand der Position, bei anderen gar nicht, weil die Spaltennamen nicht eindeutig sind, und sich damit gegenseitig überschreiben. Eindeutig wird es mit eindeutigen Aliasnamen.

    Es gibt auch Funktionen, mit denen man genaue Informationen zu den Spalten bekommt, unter anderem auch Tabellen- und Feldnamen.

    Gibt es da noch ne andere Möglichkeit außer der umständlichen Methode "... AS neuer_name"?

    Ob dir ein Alias oder das Auslesen der Spalten-Meta-Information aufwendiger erscheint, musst du selbst beurteilen.

    echo "$verabschiedung $name";

  3. Hallo Fabienne,

    leider ist meinOriginal-Posting schon im Archiv.

    ZIEL:
    alle Kundeninfos + der _eine_ Ansprechpartner für den Tourenplan, falls vorhanden + die Umsätze _mit_ dem Kunden, jeweils nur der letzte Eintrag, falls vorhanden ==> Ausgabe kommt später in eine CSV-Datei rein. Dort soll pro Zeile nur ein Kunde stehen mit den Ansprechpartnern und dem Umsatz.

    -- Kontakte, die Ansprechpartner fuer Tourenplan sind (und zwar nur einer, falls mehrere vorhanden wären)
    LEFT JOIN
      (
        SELECT k.* FROM customercontacts k WHERE k.ansprechpartner_tourenplan='ja' LIMIT 0,1
      ) kontakte
    ON customers.id = kontakte.customer_id

    Wieviele Kunden haben mehr als einen Ansprechpartner?
    Über das DB-Design kannst Du erzwingen, dass es maximal nur einen gibt, ein typischer Fall für eine 1:1-Beziehung.

    Das erspart Dir das Subselect mit dem üblen LIMIT.
    LIMIT kann ein Performance-Killer sein. Befrage EXPLAIN.

    -- Umsatz _mit_ Kunde aus Vorjahr, letzter Eintrag, nur ein Eintrag, falls mehrere vorhanden
    LEFT JOIN
      (
        SELECT u.* FROM turnover u

    Warum schon wieder SELECT *? SELECT * ist böse[tm]. Du benötigst doch nur die Spalte für den Umsatz (was) und die Spalte id für den Join.

    MySQL-Server hat sich aufgehängt, CPU 100% Last!
    Habe ich da irgendwo einen Zirkelbezug drin (Schleife??)?

    Immer hübsch der Reihe nach vorgehen, das ist gerade in diesem Fall wunderbar einfach.

    1. Teste die beiden Subselects einzeln. Schau Dir außerdem das Ergebnis von EXPLAIN an.
    2. Füge zu den Kundendaten (aus customers) sukzessive die beiden Jahresumsätze hinzu.
       Schau auf die Performance, schau auf EXPLAIN.
       Füge zum Schluß den Ansprechpartner hinzu, ein Blick auf die Performance und EXPLAIN.
    3. Füge zu den Kundendaten nur den Ansprechpartner hinzu, Blick auf die Performance und EXPLAIN.

    Optimierungsmöglichkeiten:
    Die Umsätze der Kunden aus den vergangenen Jahren sollten sich nur noch selten ändern. Hier bietet sich eine Archivierung in einer eigenen Tabelle an, die im Vergleich zum Subselect deutlich schneller sein sollte. Die Archivierung könnte grundsätzlich über einen Trigger (ab MySQL 5.0) erfolgen.

    Die id ist ja mehrfach vergeben. Wie komme ich an die einzelnen IDs?

    Über Aliasnamen. Wobei mir schleierhaft ist, welche Hilfe diverse id-Werte in einem Tourenplan darstellen.

    Nur über z.B. $array[71] anstelle $array[id]?
    Bei den einzeln definierten Vars geht das ja einfacher: $array[ansprechpartner_tourenplan]....
    Gibt es da noch ne andere Möglichkeit außer der umständlichen Methode "... AS neuer_name"?

    Ich finde Aliasnamen sehr angenehm und alles andere als umständlich.
    Allerdings verwende ich so gut wie nie das kurze und doch extrem unübersichtliche SELECT * ... und ganz besonders nicht bei Abfragen, die  hundert und mehr Spalten haben.

    Freundliche Grüße

    Vinzenz

    1. Hallo Vincenz,

      vielen Dank für Deine Antwort!

      leider ist meinOriginal-Posting schon im Archiv.

      Wieviele Kunden haben mehr als einen Ansprechpartner?

      Einige (ca. 20-30%).

      Warum schon wieder SELECT *? SELECT * ist böse[tm].

      Jop. * habe ich nur geschrieben, weil das schneller ging....
      Im "echten" Skript habe ich bereits Deinen Ratschlag mit "AS nice_name" berücksichtigt.

      1. Teste die beiden Subselects einzeln. Schau Dir außerdem das Ergebnis von EXPLAIN an.

      OK, EXPLAIN war mir neu. Sehr interessant, was da alles zum Vorschein kommt....
      Selbstverständlich habe ich die Subs getrennt getestet (sonst wäre ich gar nicht so weit gekommen).

      Viele Grüße
      Fabienne

    2. Hallo nochmals,

      momentan hänge ich an einem Subselect, welche in einem LEFT JOIN drinsteckt:

      SELECT u.erstellt_am,u.wert_gesamt,u.customer_id FROM turnover u
      WHERE u.erstellt_am=(SELECT MAX(u2.erstellt_am) FROM turnover u2 WHERE u2.customer_id=u.customer_id)

      Ergibt ein scheibar endlos laufendes Skript.....

      Kann man in einem SubSelect auf die Ergebnisse des äußerene Selects zugreifen (hier: customer_id) oder macht das Ärger?

      Vielen Dank für Eure UNterstützung!
      Fabienne

      1. Schon wieder ich:

        SELECT u.erstellt_am,u.wert_gesamt,u.customer_id FROM turnover u
        WHERE u.erstellt_am=(SELECT MAX(u2.erstellt_am) FROM turnover u2 WHERE u2.customer_id=u.customer_id)
        Ergibt ein scheibar endlos laufendes Skript.....

        Liegt das evtl daran, dass in dem Subselect (SELECT MAX(u2.erstellt_am)....) mehrer Datensätze gefunden werden könnten?
        Eigentlich sollte diese Abfrage mir doch nur einen einzigen Wert zurückliefern und zwar den maximalen Datumswert.

        Oder liege ich das falsch?

        Viele Grüße
        Fabienne

        1. Hallo Fabienne,

          SELECT u.erstellt_am,u.wert_gesamt,u.customer_id FROM turnover u
          WHERE u.erstellt_am=(SELECT MAX(u2.erstellt_am) FROM turnover u2 WHERE u2.customer_id=u.customer_id)
          Ergibt ein scheibar endlos laufendes Skript.....

          Liegt das evtl daran, dass in dem Subselect (SELECT MAX(u2.erstellt_am)....) mehrer Datensätze gefunden werden könnten?

          nein, das sollte kein Problem darstellen. Das ist eine ganz normales korreliertes Subselect, das sich im Vergleich zu einer LIMIT-Klausel gerade dadurch auszeichnet, dass es _keine_ Werte unterschlägt :-)

          Eigentlich sollte diese Abfrage mir doch nur einen einzigen Wert zurückliefern und zwar den maximalen Datumswert.

          Oder liege ich das falsch?

          wenn beim letzten Datumswert mehr als ein Umsatz vorliegt, dann kann es auch mehr als einen Wert zurückliefern. In Deinem Fall möchtest Du definitiv nur einen Wert haben, also musst Du auch Kriterien haben, die Dir diesen Wert exakt zurückliefern. Gibt es mehrere Umsätze am gleichen Tag, dann reicht der Tag nicht aus. Gibt es mehrere Umsätze in der gleichen Sekunde, dann reicht DATETIME in MySQL nicht aus :-)

          Korrelierte Subselects können zeitaufwendig sein, insbesondere wenn das Ergebnis in einem Join genutzt wird - und kein Index genutzt werden kann.

          Für diesen Fall solltest Du Dir überlegen, ob Du nicht mit

          a) temporären Tabellen bzw. zwei Views oder
          b) einer Archivtabelle

          besser fährst.

          Es wäre nett zu wissen, welche MySQL-Version im Einsatz ist. Views, Trigger und weitere feine Sachen stehen erst ab MySQL 5.0.x zur Verfügung.

          Freundliche Grüße

          Vinzenz

          1. Hi Vinzenz,

            vielen Dank für Deine Hilfe!

            Es wäre nett zu wissen, welche MySQL-Version im Einsatz ist. Views, Trigger und weitere feine Sachen stehen erst ab MySQL 5.0.x zur Verfügung.

            MySQL 4.1.10

            Wenn ich nach der MAX(ID) suche geht es auch nicht (die ID gibt es nur ein einziges Mal!).

            Viele Grüße
            Fabienne

          2. Hallo und guten Abend,

            ich habe nochmals eine Anmerkung:

            Folgendes funktioniert auch nicht (MAX(id)):

            -- Umsatz aus Vorjahr
            LEFT JOIN
              (
                SELECT u.wert_gesamt,u.customer_id FROM turnover u
                    WHERE
                       u.id=(SELECT MAX(u2.id) FROM turnover u2
                                WHERE
                                  u2.customer_id=u.customer_id
                                 AND
                                  u2.jahr='2007')
                        AND
                          u.jahr='2007'
              ) umsatz_vorjahr
            ON customers.id = umsatz_vorjahr.customer_id

            Aber: Das funktioniert:

            -- Kontakte einsammeln
            LEFT JOIN
              (
                SELECT k.name,k.customer_id,k.ansprechpartner_tourenplan FROM customercontacts k
                          WHERE
                            k.ansprechpartner_tourenplan='ja'
                            AND k.id=(SELECT MIN(k2.id) FROM customercontacts k2 WHERE k.ansprechpartner_tourenplan='ja' AND k2.customer_id=k.customer_id)

            ) kontakte
            ON customers.id = kontakte.customer_id

            Vom Prinzip her sind beie Abfragen gleich.
            Aber: Die erste funktioniert nur solange, solange im SUBSELECT kein Bezug zunm Hauptselect über u2.customer_id=u.customer_id hergestellt wird. Die zweite funktioniert immer - auch mit k2.customer_id=k.customer_id

            Darf man im SUBSELECT mit LIMITs arbeiten?

            Idee:

            -- Umsatz aus Vorjahr
            LEFT JOIN
              (
                SELECT u.wert_gesamt,u.customer_id FROM turnover u
                    WHERE
                       u.id=(SELECT u2.id FROM turnover u2
                                WHERE
                                  u2.customer_id=u.customer_id
                                 AND
                                  u2.jahr='2007' ORDER BY u2.id LIMIT 0,1)
                        AND
                          u.jahr='2007'
              ) umsatz_vorjahr
            ON customers.id = umsatz_vorjahr.customer_id

            Ja, es funktioniert, aber:
            Es geht wieder nur solange, bis die Relation zwischen u.customer_id=u2.customer_id hergestellt wird. Danach läuft das Skript sich tot.

            MySQL 4.1.10
            Indexe sind auf die jeweiligen Spalten gesetzt.

            Hat sonst noch jemand eine Idee?
            Bin am verzweifeln....

            Viele Grüße
            Fabienne

            1. Schon wieder ich,...

              Es funktioniert prizipiell schon so:
              (Bitte nicht schimpfen wegen den *-chen)

              SELECT
                  customers.*,
                  umsatz_vorjahr.*
              FROM
                  customers

              -- Umsatz aus Vorjahr
              LEFT JOIN
                (
                  SELECT u.* FROM turnover u
                      WHERE
                         u.id=(SELECT MAX(u2.id) FROM turnover u2
                                  WHERE
                                    u2.jahr='2006'
              AND u2.customer_id=u.customer_id)
                          AND
                            u.jahr='2006'
                ) umsatz_vorjahr
              ON customers.id = umsatz_vorjahr.customer_id

              Aber scheinbar packt das meine DB / Server nicht.
              Es sind ca.10.000 Datensätze in customers und ca.30.000 Datensätze in turnover.

              Sind solche SubSELCTs so extreme Performance-Killer (mit einer kleinen von Hand aufgesetzten Datenbank mit 10 Einträgen klappt alles!)?

              Wer hat Ideen das ganze zu optimieren?
              Wäre es sinnvoll aich alle Datensätze (also ohne Subselect) ausspucken zu lassen und anschließen innerhalb der PHP-Schleife die gewünschten Werte raussuchen?
              Das kann doch aber nicht sein, dass eine DB mit den "paar" Datensätzen schon schlapp macht?

              Ich würde mich freuen, wenn Ihr mir Tipps geben könntet!

              Viele Grüße
              Fabienne

              1. Ich glaube - ich habs:

                Man muss über _alle_ Spalten, die im SubSELECT enthalten sind, einen gemeinsamen Index legen (nicht über die Spalten einzeln!!).

                Beispiel:
                ALTER TABLE turnover ADD INDEX ( jahr , customers\_id ) ;

                Oder in PHPMyAdmin:
                In der Struktur-Übersicht ganz links die Häckchen der betroffenen Spalten setzen und unter der Überscihtstabelle den Button "Index" anklicken.

                Danach klappt das auch mit der Performance!

                Hoffe, dass die Lösung diese "schrecklichen" Stunden auch jemand anders nützlich sind!

                Viele Grüße
                Fabienne

                1. Man muss über _alle_ Spalten, die im SubSELECT enthalten sind, einen gemeinsamen Index legen (nicht über die Spalten einzeln!!).

                  Kleine Korrektur:
                  Index nur über die Spalten, die in der WHERE-Klausel verarbeitet werden!