Please ensure that you have an index on tbValue (NID, DID, VID)
After ensuring this index is attached to tbValue, see which of the following performs better:
/* here's rewritten using proper derived table... */
SELECT
tbPoint.Nname
, tbPoint.Nidx
, tbPoint.NID
, tbPoint.Nunit
, v1.Value
, v1.VtimeStamp
FROM tbScanList
INNER JOIN tbPoint
ON tbScanList.TID = tbPoint.TID
INNER JOIN tbValue v1
ON tbPoint.NID = v1.NID
INNER JOIN
(
SELECT NID, MAX(VID) AS LastUpdate
FROM tbValue
WHERE DID = 42
GROUP BY NID, DID
) AS v2
ON v1.NID = v2.NID
AND v1.VID = v2.LastUpdate
WHERE tbScanList.DID = 42
ORDER BY tbPoint.Nname;
/* or with correlated subquery... */
SELECT
tbPoint.Nname
, tbPoint.Nidx
, tbPoint.NID
, tbPoint.Nunit
, v1.Value
, v1.VtimeStamp
FROM tbScanList
INNER JOIN tbPoint
ON tbScanList.TID = tbPoint.TID
INNER JOIN tbValue v1
ON tbPoint.NID = v1.NID
AND v1.DID = 42
AND v1.VID = (SELECT MAX(VID) FROM tbValue WHERE NID = v1.NID AND DID = 42)
WHERE tbScanList.DID = 42
ORDER BY tbPoint.Nname;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com