max und group by liefert falsche werte?!

[Chrischaaan]

hallo zusammen,

ich steh grade wieder mal aufm schlauch.

ich erhalte mit folgender abfrage die angegebenen werte:

Abfrage

SELECT a.objectno,(a.msg_time), a.user_text, a.enum, b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a, tbl_ttw_orderreport b
WHERE a.orderno = b.orderid
AND
(
a.enum = 'Container abgeholt'
OR
a.enum = 'Container geliefert'
)

werte

010	2010-01-04T15:01:13Z	 		Container abgeholt	49434961	11061199	Nürnberg, Maybachstraße					63
007	2010-01-18T19:31:33Z	con12euro	Container geliefert	51002388	11764388	(DE) 07619 Schkölen, Wetzdorf 64			TL180110_002
007	2010-01-18T13:57:50Z	Con01-test	Container abgeholt	49367438	11166669	(DE) 90530 Röthenbach Bei Sankt Wolfgang (Wendelst...	TA180110_001
007	2010-01-18T22:31:50Z	con10euro	Container geliefert	53540980	10228988	(DE) 21509 Glinde (Stormarn), Osterfeld			TL180110_004
007	2010-01-19T13:03:09Z	con10euro	Container abgeholt	53540980	10228988	(DE) 21509 Glinde (Stormarn), Osterfeld			TA190110_002
007	2010-01-19T16:46:19Z	con10euro	Container geliefert	53540980	10228988	(DE) 21509 Glinde (Stormarn), Osterfeld			TL190110_001
007	2010-01-20T11:14:30Z	con10euro	Container abgeholt	53540980	10228988	(DE) 21509 Glinde (Stormarn), Osterfeld			TA200110_001
007	2010-01-20T11:24:55Z	con10euro	Container geliefert	51340319	6558580		(DE) 47798 Krefeld, Sternstraße 30			TL200110_001

nun will ich die datensätze wo von einer artikelnummer (user_text) die jeweils höchste (also späteste) zeit vorhanden ist. also wäre das von der artikelnummer con10euro der letzte datensatz weil da die späteste zeit vorliegt...

ich erhalte mit der abfrage

SELECT a.objectno, max(a.msg_time), a.user_text, a.enum, b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a, tbl_ttw_orderreport b
WHERE a.orderno = b.orderid
AND
(
a.enum = 'Container abgeholt'
OR
a.enum = 'Container geliefert'
)
GROUP BY user_text

die folgenden werte

010	2010-01-04T15:01:13Z	 		Container abgeholt	49434961	11061199	Nürnberg, Maybachstraße					63
007	2010-01-18T13:57:50Z	Con01-test	Container abgeholt	49367438	11166669	(DE) 90530 Röthenbach Bei Sankt Wolfgang (Wendelst...	TA180110_001
007	2010-01-20T11:24:55Z	con10euro	Container geliefert	53540980	10228988	(DE) 21509 Glinde (Stormarn), Osterfeld			TL180110_004
007	2010-01-18T19:31:33Z	con12euro	Container geliefert	51002388	11764388	(DE) 07619 Schkölen, Wetzdorf 64			TL180110_002

nun meine frage: warum steht bei dem datensatz wo der user_text "con10euro" ist, nicht der richtige ort bei destination?
der datensatz mit dieser msg_time hat nen anderen ort...

wäre für hilfe dankbar! weiß wieder mal nicht mehr weiter.

bye, Christian.

[Novi]

Hi,

ich hab deinen Beitrag jetzt nur mal überflogen:

Ich würde den Join hier erst einmal explizit hinschreiben, aber das ist an sich kein Fehler.

Dein Fehler müsste darin liegen, das du nur nach user_text gruppierst. Die anderen Werte (a.objectno, a.enum, b.latitude, b.longitude, b.destination, b.orderid) aber trotzdem abfragst. Diese Werte dürften einfach irgendwelche sein und müssen nicht zu max(a.msg_time) passen. Das die Abfrage überhaupt funktioniert ist eine Besonderheit von MySql.

Die Lösung ist eine Unterabfrage, in der du nur max(a.msg_time) abfragst und die zurückgegeben Werte als Bedingung für deine eigene Abfrage verwändest.

Ansonsten möchte ich dich auf folgenden Thread verweisen: http://entwickler-forum.de/showthread.php?t=58569

[Chrischaaan]

hallo,

in der unterabfrage die max_msg_time abzufragen habe ich schon versucht. war an sich auch erfolgreich. nur dauert dann die komplette abfrage für diese paar werte über 2 minuten...

wie geht das schneller?

[Novi]

Verwendest du eine Having- oder eine Where-Klausel?

Poste bitte mal die langsame aber funktionierende Abfrage.

[Chrischaaan]

nix dergleichen.

SELECT a.objectno, a.msg_time, a.user_text, a.enum, b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a, tbl_ttw_orderreport b
WHERE a.orderno = b.orderid
AND msg_time IN
(
	SELECT MAX(msg_time)
	FROM tbl_ttw_messages
	WHERE
	(
		enum = "Container geliefert"
		OR
		enum = "Container abgeholt"
	)
	group by user_text
)

inzwischen habe ich das problem soweit gelöst. habe die unterabfrage in ne sicht gepackt und noch bissl dran rumgebastelt.
das funktioniert jetzt eigentlich wie es soll auch wenn ich die lösung nicht für sonderlich elegant halte.

[Novi]

Sry ich meinte natürlich mit ON statt mit WHERE und nicht mit HAVING.

[highlight=sql]
SELECT a.objectno, a.msg_time, a.user_text, a.enum, b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a
LEFT JOIN tbl_ttw_orderreport b On a.orderno = b.orderid
WHERE msg_time IN
(
SELECT MAX(msg_time)
FROM tbl_ttw_messages
WHERE
(
enum = "Container geliefert"
OR
enum = "Container abgeholt"
)
group by user_text
)
[/highlight]

Ich bin mir nicht sicher, ob die Variante schneller ist, aber ich könnte es mir vorstellen.

[Falk Prüfer]

Hallo,

also ob diese Abfrage wirklich in jedem Fall die korrekten Werte liefert wage ich zu bezweifeln. Es wird nirgendwo eine exakte Beziehung zwischen der inneren und der äußeren Abfrage hergestellt. Es wird lediglich geprüft ob sich die aktuelle msg_time in der Menge der maximalen msg_time zu user_text befindet. Welche dann die Übereinstimmung erzielt ist dabei völlig egal und das kann unmöglich richtig und gewollt sein.

Mein Vorschlag:
[highlight=sql]
SELECT a.objectno, a.msg_time, a.user_text, a.enum,
b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a
LEFT JOIN tbl_ttw_orderreport b ON a.orderno = b.orderid
WHERE a.msg_time = (
SELECT MAX(i.msg_time)
FROM tbl_ttw_messages i
WHERE i.user_text = a.user_text
AND i.enum IN ('Container geliefert', 'Container abgeholt')
)
[/highlight]

Gruß Falk

P.S.: String-Literale werden in SQL in einfache ' Hochkomma und nicht in doppelte " geschrieben.

[Chrischaaan]

hallo zusammen,

das mit der prüfung von "msg_time IN" etc. hatte ich mir auch schon überlegt und das IN dann durch = ersetzt. trotzdem dauert die abfrage ewig. auch die varianten von falk und novi dauern extrem lang (trotzdem schonmal vielen dank für euer bemühen!) falks abfrage dauerte eben 263sek. ...

ich hab jetzt eine sicht gemacht, welche mir unter anderem die letzten nachrichtenzeiten (msg_time) liefert:

[highlight=sql]
CREATE VIEW vw_ttw_container_count AS
SELECT orderno, objectno, enum, user_text, COUNT(*) AS anzahl
FROM tbl_ttw_messages
WHERE
(
enum = "Container geliefert"
OR
enum = "Container abgeholt"
)
GROUP BY user_text, enum
[/highlight]

[highlight=sql]
CREATE VIEW vw_ttw_container_maxtime AS
SELECT MAX(msg_time) AS msg_time, msg_text AS msg_text, orderno AS orderno, enum AS enum, user_text AS user_text
FROM tbl_ttw_messages
WHERE enum = 'Container geliefert'
OR enum = 'Container abgeholt'
GROUP BY user_text
[/highlight]

dann frage ich die msg_time nicht mehr als unterabfrage ab sondern hol sie mir eben aus der sicht. das geht schneller.

[highlight=sql]
CREATE VIEW vw_ttw_container_lastdest AS
SELECT a.objectno, a.msg_time, a.user_text, a.enum, b.latitude, b.longitude, b.destination, b.orderid
FROM tbl_ttw_messages a, tbl_ttw_orderreport b, vw_ttw_container_maxtime c
WHERE a.orderno = b.orderid
AND a.msg_time = c.msg_time
AND
(
a.enum = "Container geliefert"
OR
a.enum = "Container abgeholt"
)
[/highlight]

und dann bastle ich mir mein ergebnis aus der vorherigen sicht...

[highlight=sql]
CREATE VIEW vw_ttw_container_anzeige AS
SELECT b.addrid, c.msg_time, c.objectno, c.latitude, c.longitude, c.destination, c.user_text AS conname, c.enum
FROM vw_ttw_container_count a, tbl_ttw_container_zuweisung b, vw_ttw_container_lastdest c
WHERE b.orderid = c.orderid
GROUP BY c.user_text
[/highlight]

das liefert mir jetzt die werte wie ich sie brauche.
ich weiß bloß nicht, ob das immer so funktioniert weil ich teilweise die joins nicht komplett verknüpft hab. zum beispiel in der letzten sicht... problem ist halt, dass ich eigentlich auf die orderid (orderno) verknüpfen muss weil die eindeutig ist. aber dadurch, dass die ja nicht immer überall "da" ist funktioniert das nicht und der hat mir werte raus die ich brauche.
die ist deswegen nicht "da" weil ich durch die nutzung der GROUP BY funktion in den ersten zwei sichten immer die älteste (oder sonst irgendeine orderid) gewählt wird und in container_lastdest die eben letzte und dann hab ich in der ergebnismenge keine zuordnung mehr...

muss halt heute noch testen ob das hinhaut. so richtig überzeugt mich meine lösung allerdings nicht...

soweit so gut. vielen dank schonmal für eure bisherige hilfe und die weiteren anrgegungen die bestimmt noch kommen werden :-)

bye, Christian.

[Falk Prüfer]

Hallo,

wenn eine Subquery mit WHERE ... IN sehr lange dauert (weil sie ggfs. sehr viele Ergebnisse liefert), dann hilft u.U. ein Umschreiben auf eine Subquery der Form WHERE EXISTS ...
In deiner Subquery kann zudem kein Index benutzt werden, da die einzelnen Teile Oder-Verküpft sind. Du könntest hier versuchen mit einer zusätzlichen Abfrage die Verwendung eines Index (natürlich nur wenn ein passender existiert) zu erzwingen bzw. die Anzahl der DS schneller einzuschräncken.

bspw. statt:
[highlight=sql]
...
WHERE
(
enum = "Container geliefert"
OR
enum = "Container abgeholt"
)
[/highlight]
besser:
[highlight=sql]
...
WHERE
enum like 'Container %'
and
enum in ('Container geliefert', 'Container abgeholt')
[/highlight]
Auch ist es natürlich ratsam das "Schlüselfeld" user_text zu indizieren.

Gruß Falk

[Chrischaaan]

hi zusammen,

ich hab jetzt mal deine abfrageoptimierungstipps angewandt falk, und bin beeindruckt wieviel das bringt wenn man so kleine dinge umsetzt. ist echt wesentlich schneller jetzt schon :-)

vielen dank soweit. die sache läuft jetzt auch wie sie soll.

bye, Christian.