Hi,
Just wondering if anyone could help me optimise this query, it can take
up to 3 seconds to execute which makes me think theres probably a
better way to do what i'm doing..
SELECT MAX(Latitude) as "MaxLat",MIN(La titude) as
"MinLat",MAX(Lo ngitude) as "MaxLong",Min(L ongitude) as "MinLong",
count(filtered. serialno) as "count"
FROM (
SELECT tl.*
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.no deid AND tl.datetimestam p < t2.datetimestam p
WHERE t2.nodeid IS NULL) as filtered
JOIN usernodes un on (filtered.nodei d = un.nodeid)
WHERE un.userid = 99;
Basically i'm trying to get the maximum / minimum latitude and
longitude values from a position log for a particular user , (who may
have > 1 node giving lat long data)...
I've indexed every field used in the where/join statements.
Any advice appreciated, thanks
Just wondering if anyone could help me optimise this query, it can take
up to 3 seconds to execute which makes me think theres probably a
better way to do what i'm doing..
SELECT MAX(Latitude) as "MaxLat",MIN(La titude) as
"MinLat",MAX(Lo ngitude) as "MaxLong",Min(L ongitude) as "MinLong",
count(filtered. serialno) as "count"
FROM (
SELECT tl.*
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.no deid AND tl.datetimestam p < t2.datetimestam p
WHERE t2.nodeid IS NULL) as filtered
JOIN usernodes un on (filtered.nodei d = un.nodeid)
WHERE un.userid = 99;
Basically i'm trying to get the maximum / minimum latitude and
longitude values from a position log for a particular user , (who may
have > 1 node giving lat long data)...
I've indexed every field used in the where/join statements.
Any advice appreciated, thanks
Comment