Cronjob-Abfrage aus zwei Tabellen

[Overtone]

Ich möchte einen Cronjob erstellen, welcher alle Kunden eines XT-Commerce-Shop v4.2 anschreibt, welche seit 5 Monaten keinen Einkauf mehr getätigt haben. Leider hänge ich (mal wieder) am ermitteln der MAX(orders_id) der Kunden. Zu meiner bisherigen Vorgehensweise:

Ich frage zunächst die orders_id zu allen Bestellungen in der xt_orders_status_history ab, welche das Datum von heute vor 5 Monaten haben (Variable $today2):

SELECT orders_id FROM xt_orders_status_history
                    WHERE
                        orders_status_id = '16' AND
                        date_added LIKE '%$today2%'

Die orders_status_id = '16' tut hier nicht viel zur Sache, bedeutet aber bei einem XT-Commerce-Shop in der Regel den Status "Offen", also gerade vom Kunden bestellt und vom Shop-Inhaber noch nicht weiter bearbeitet.

Innerhalb einer While-Schleife (geht sicher auch allles innerhalb einer einzigen Abfrage) erstelle ich eine weitere Abfrage, welche zu der jeweiligen orders_id die entsprechende customers_id ermittelt, da diese in der Tabelle xt_orders_status_history nicht existiert:

SELECT orders_id, customers_id FROM xt_orders
                    WHERE
                        orders_id = '$orders_id' AND
                        orders_id = (SELECT MAX(orders_id) FROM xt_orders WHERE ...

Und genau an dieser Stelle hänge ich jetzt. Ich muss praktisch prüfen, dass die verwendete orders_id bei dem Kunden mit der damit verbundenen customers_id auch die letzte Bestellung dieses Kunden (MAX(orders_id)) war. Ansonsten soll dieser ja NICHT angeschrieben werden, da mind. eine weitere Bestellung existiert, die noch keine 5 Monate her ist! Zäume ich das Pferd mit meinen Abfragen hier von der falschen Seite auf? Ich hätte halt gerne vermieden, dass ich erst ein Array erstelle, welches alle MAX(orders_id) zu jedem Kunden ermittelt und in einem Array speichert, da bei diesem Shop knapp 100.000 Bestellungen von 50.000 Kunden vorliegen. Ansonsten wäre sicherlich mit dieser Abfrage ein Array-Vergleich möglich:

SELECT *
          FROM `table`
         WHERE `id` IN ('Array') ...

[Overtone]

Ich habe gerade folgende Abfrage im PhpMySqlAdmin eingegeben und promt die Datenbank überlastet:

SELECT orders_id FROM xt_orders_status_history
                    WHERE
                        orders_id IN (SELECT MAX(orders_id) FROM xt_orders GROUP BY customers_id) AND
                        date_added LIKE '%2015-06-16%'

Der Subselect "SELECT MAX(orders_id) ..." erzeugt über 60.000 Datensätze. Das ist wohl deutlich zu viel für ein Array, oder?

Nachtrag: Ich habe diese Abfrage gerade in einem Testshop mit wenigen Bestellungen getestet. Das funktioniert dort hervorragend und liefert genau das gewünschte Teilergebnis. Wie bekomme ich das jetzt aber umgestellt, damit kein überdimensionales Array entsteht?

[erc]

Du kannst den Query als Subquery nehmen...

SELECT * FROM
(
    SELECT orders_id FROM xt_orders_status_history                WHERE
                        orders_id IN (SELECT MAX(orders_id) FROM xt_orders GROUP BY customers_id) AND
                        date_added LIKE '%2015-06-16%'
) AS t INNER JOIN
`was_auch_immer_für_andere_daten` AS table (t.orders_id = table.orders_id)

[Perry Staltic]

SELECT ...AND
date_added LIKE '%2015-06-16%'

Was ergibt denn an dieser Bedingung, dass es vor 5 Monaten gewesen sein soll?
Eine Likeabfrage auf einem Datum finde ich auch "etwas" schräg. Aber in mysql ist das ja gerne mal vom Typ Text oder wie ist das in Deiner DB? Man kann bei Datumstypen prima mit >,<, between usw. arbeiten.
Ansonsten sollte man schon dafür sorgen, dass es nicht einfach alles von vor 5 Monaten ist.
Ich wär jedenfalls angepi..., wenn ich ab Monat 5 ständig Emails von irgendeinem blöden Shop bekomme.

[Overtone]

Dein Einwand ist absolut korrekt. Ich habe das hier der Einfachheit halber nicht näher erläutert, aber die Abfrage

date_added LIKE '%2015-06-16%'

bleibt natürlich nicht statisch sondern wird aus einer Datums-Variablen erzeugt, welche bei der täglichen Cronjob-Auslösung den aktuellen Tag minus 5 Monate errechnet. So erhält jeder Kunde definitiv nur nach 5 Monaten ohne Bestellung eine E-Mail. Kauft er dann wieder ein erhält er natürlich erneut nach 5 Monaten eine entsprechende E-Mail. Aber nicht öfters oder gar täglich ab 5 Monaten! Das Bestelldatum liegt hier aber tatsächlich nicht als Typ Text sondern korrekterweise als Typ Datum vor. Jedoch benötige ich für dieses Anliegen keinerlei datenbankmäßige Datumsberechnung.

[Overtone]

Du kannst den Query als Subquery nehmen...

SELECT * FROM
(
SELECT orders_id FROM xt_orders_status_history WHERE
orders_id IN (SELECT MAX(orders_id) FROM xt_orders GROUP BY customers_id) AND
date_added LIKE '%2015-06-16%'
) AS t INNER JOIN
`was_auch_immer_für_andere_daten` AS table (t.orders_id = table.orders_id)

Hallo erc,

ich glaube nicht, dass damit mein Problem gelöst wird. Das Array würde doch auch in diesem Fall knapp 60.000 Variablen beinhalten, oder? Meine Grundidee war diese Abfrage umzukehren, also (in Stichworten):
Wie viele Bestellungen (orders_id) in der Datenbanktabelle xt_orders_status_history beinhalten das Datum 2015-06-16 (ergibt ca. 100 Bestellungen pro Tag).
Jetzt das entscheidende:
Wie viele dieser Bestellungen (orders_id) sind jeweils die letzte Bestellung eines Kunden?
Dies jetzt aber nicht mittels "MAX(orders_id) FROM xt_orders GROUP BY customers_id", sonst haben wir ja wieder ca. 60.000 Variablen, sondern, vereinfacht und in Stichworten ausgedrückt:
Ist die erste gefundene Bestellung (orders_id) die neuesete/letzte Bestellung des Kunden mit der damit verbundenen customers_id...
Ist die zweite gefundene Bestellung (orders_id) die neuesete/letzte Bestellung des Kunden mit der damit verbundenen customers_id...
usw.
Somit müssten nur ca. 100 statt 60.000 Abfragen durchgeführt werden!

Das wäre alles!

[erc]

Was den für ein Array? Das läuft alles in der Datenbank ab. was_auch_immer_für_andere_daten wären in deinem Fall die Kundendaten. Als Resultset bekommst du deine 100 Kunden und kannst die Mails verschicken. Diese 60.000 Datensätze die da zwischenzeitlich als Temporäre Tabelle anfallen sind halb so wild wenn du den Query nur einmal am Tag ausführst. Du solltest nur drauf achten das du mindestens Mysql 5.6 benutzt, ansonsten ist das mit dem IN (SELECT ...) nicht so gut.
Du kannst den Query aber auch generell umschreiben wenn du nur gezielt für einen Tag suchst.

SELECT ... FROM bestellungen LEFT JOIN bestellungen AS n ON (bestellungen.customer_id = n.customer_id AND n.order_id > bestellungen.order_id) WHERE bestellung.datum = 'vor 5 Monaten' AND n.order_id IS NULL

PS: Rechtslage beachten. Ohne Einwilligung darfst du den Kunden nicht per Mail bewerben.

[Overtone]

Wie bereits oben erwähnt: Ich habe gerade folgendes in den phpMyAdmin des Kunden eingegeben und alles hängt komplett. Der Kunde betreibt einen erstklassigen Server bei einem Premium-Anbieter und speziell die Datenbank ist in der Regel deutlich schneller als meine Kunden-Server bei Strato oder 1&1:

SELECT orders_id FROM xt_orders_status_history
                    WHERE
                        orders_id IN (SELECT MAX(orders_id) FROM xt_orders GROUP BY customers_id) AND
                        date_added LIKE '%2015-06-16%'

Ergänzung: Nach ca. 3 Mnuten kommt diese Meldung: "#2013 - Lost connection to MySQL server during query"!
Ih habe jetzt mal testweise in das Subselect "LIMIT 200" eingesetzt und erhalte folgende Meldung: "#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"!
Betrifft das jetzt tatsächlich nur das "LIMIT" oder auch das hier angesprochene "mindestens Mysql 5.6 in Verbindung mit dem IN (SELECT ...)?

[Perry Staltic]

Also erc hat einen Post höher schon eine alternative Formulierung angegeben. Jedes WHERE IN (SELECT .. kannst Du auch in einen Join umwandeln.
Und nochmal, das Like ist Dreck, besonders mit einem % vorne. Was soll da stehen?
Wenn Du ein Like mit Platzhaltern auf einen Nicht String Typ anwendest, müssen alle Vergleichswerte zunächst aus diesem Typ (hier date) nach String umgewandelt werden, damit die Like Operation überhaupt angewendet werden kann.
- das kostet Zeit (die Umwandlung)
- das kostet Extrazeit, weil ein Index -sofern vorhanden- nicht mehr nutzbar ist, bedeutet > Fulltablescan
- das ist fehleranfällig, weil ein Defaultdatumsformat angewendet wird (was konfigurationsabhängig ist)
Das alles ohne Not. Du hast die Datumsgrenzen doch exakt festgelegt.

[Overtone]

Sehr interessant! Es macht also einen großen Performance-Unterschied ob ich beispielsweise

WHERE date_added LIKE '%2015-06-16%'

oder

WHERE date_added LIKE '2015-06-16%'

oder

WHERE date_added BETWEEN '2015-06-16 00:00:00' AND '2015-06-16 23:59:59'

einsetze? Wäre meine BETWEEN-Klausel so korrekt? Ich finde in den Foren nämlich verschiedene Konstrukte, welche noch Klammern und/oder CAST einsetzen! Funktionieren tut diese Abfrage jedenfalls ohne Fehler.

Wow, ich habe hier mal etwas "getestet":
Die Abfrage (bei immerhin über 300.000 Datensätzen in dieser Tabelle) mit der BETWEEN-Klausel dauert 0.0001 Sekunden, die Abfrage mit "LIKE '2015-06-16%'" (also ohne "%" vor dem Jahr) dauert ebenfalls 0.0001 Sekunden (manchmal 0.0002 Sekunden), die Abfrage mit "LIKE '%2015-06-16%'" braucht im günstigesten Fall 0.0579 Sekunden. Das ist fast 600 mal so lange!!! Hätte ich nie erwartet. Mir ist natürlich klar, dass vor dem Jahr in einem Datumsfeld niie etwas stehen kann. Aber dass das solche Auswirkungen hat? Und das ist eine Hammer-schnelle Datenbank! Bei umfangreicheren Abfragen kann das schlussendlich ja sicherlich einige Sekunden ausmachen.

[Perry Staltic]

Sehr interessant! Es macht also einen großen Performance-Unterschied ob ich beispielsweise

WHERE date_added LIKE '%2015-06-16%'

oder

WHERE date_added LIKE '2015-06-16%'

oder

WHERE date_added BETWEEN '2015-06-16 00:00:00' AND '2015-06-16 23:59:59'

einsetze? Wäre meine BETWEEN-Klausel so korrekt? Ich finde in den Foren nämlich verschiedene Konstrukte, welche noch Klammern und/oder CAST einsetzen! Funktionieren tut diese Abfrage jedenfalls ohne Fehler.

Ja, macht es. BETWEEN an sich ist korrekt, aber Du gibst das immer noch als String an und nicht als Date.
Hab jetzt keinen Bock noch mal rauf zu lesen, aber irgendwo hast Du geschrieben, es sei ein echter Datetyp. Dann solltest Du auch Date übergeben an das BETWEEN. Das ginge vielleicht mit CAST, bin nicht so sattelfest mit mysql. Ich hätte eher an STR_TO_DATE('2015-06-16', '%Y-%m-%d') gedacht. Musst Du mal in die Doku schauen. Aber aufgepasst, das macht nur und genau dann Sinn, wenn Dein Feld ein DATE(-time) ist. Statt der Uhrzeit würde ich dann im 2. Vergleich den nächsten Tag nehmen, sollte passen.
Ist Dein date_added Feld varchar oder so, dann ist das zwar furchtbar, aber STR_TO_DATE ist dann natürlich Blödsinn.

Falls Dich Hintergründe dazu interessieren, lies mal etwas über die Funktionsweise herkömmlicher Indizierung. Oder auch etwas zum Thema impliziter und expliziter Konvertierung von Typen.

[Overtone]

Vielen Dank an euch alle. Bin jetzt mit der Abfrage klar gekommen. Cronjob sendet ordnungsgemäß!