Optimizing help

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


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(Latitude) as
"MinLat",MAX(Longitude) as "MaxLong",Min(Longitude) as "MinLong",
count(filtered.serialno) as "count"
FROM trackinglogs tl LEFT JOIN trackinglogs AS t2
ON tl.nodeid=t2.nodeid AND tl.datetimestamp < t2.datetimestamp
WHERE t2.nodeid IS NULL) as filtered
JOIN usernodes un on (filtered.nodeid = 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

Re: Optimizing help

Can you post:

1) the EXPLAIn of that query
2) SHOW INDEX for the tables
3) DESCRIBE of the tables

Thank you!

Site Timeline