我在MySQL中选择了以下内容，它会产生正确的结果，但是执行起来会花费不必要的时间：
SELECT tblGPSDevices.Email, tblLoc.Lat, tblLoc.Lon, tblLoc.Radius, tblLoc.CreationTimeStamp, tblTrackedUsers.ID, tblTrackedUsers.TrackerDeviceID, tblTrackedUsers.TrackedDeviceID
FROM tblTrackedUsers
INNER JOIN tblGPSDevices ON tblTrackedUsers.TrackedDeviceID = tblGPSDevices.ID
LEFT OUTER JOIN (
SELECT A.DeviceID, A.Lat, A.Lon, A.Radius, A.CreationTimeStamp, A.ID
FROM tblLocations A
INNER JOIN (
SELECT DeviceID, MAX(CreationTimeStamp) AS CreationTimeStamp, MAX(ID) AS ID
FROM tblLocations
GROUP BY DeviceID
) AS B ON A.DeviceID = B.DeviceID
AND A.CreationTimeStamp = B.CreationTimeStamp
AND A.ID = B.ID
) AS tblLoc ON tblLoc.DeviceID = tblGPSDevices.ID
WHERE tblGPSDevices.Validated = 0x01
AND tblGPSDevices.Enabled = 0x01
AND tblTrackedUsers.Validated = 0x01
AND tblTrackedUsers.TrackerDeviceID = 1
ORDER BY tblTrackedUsers.ID;
该查询的运行速度比正常情况要慢得多，因为它会对tblLocations进行全表扫描。
这是真正减慢查询速度的部分：
SELECT A.DeviceID, A.Lat, A.Lon, A.Radius, A.CreationTimeStamp, A.ID
FROM tblLocations A
INNER JOIN (
SELECT DeviceID, MAX(CreationTimeStamp) AS CreationTimeStamp, MAX(ID) AS ID
FROM tblLocations
GROUP BY DeviceID
) AS B ON A.DeviceID = B.DeviceID
AND A.CreationTimeStamp = B.CreationTimeStamp
AND A.ID = B.ID
这是解释计划：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tblTrackedUsers ref TrackerDeviceID,TrackedDeviceID TrackerDeviceID 9 const 14 Using where; Using temporary; Using filesort
1 PRIMARY tblGPSDevices eq_ref PRIMARY PRIMARY 8 tblTrackedUsers.TrackedDeviceID 1 Using where
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2073
2 DERIVED <derived3> ALL NULL NULL NULL NULL 2073
2 DERIVED A eq_ref PRIMARY,DeviceID,CreationTimeStampIndex PRIMARY 8 B.ID 1 Using where
3 DERIVED tblLocations index NULL DeviceID 8 NULL 174058
即使我只需要该表中DeviceID的一小部分，它也会对tblLocations进行全表扫描。
我只需要查看从此部分返回的DeviceID：
INNER JOIN tblGPSDevices ON tblTrackedUsers.TrackedDeviceID = tblGPSDevices.ID
WHERE tblTrackedUsers.TrackerDeviceID = 1
但是很遗憾，tblTrackedUsers.TrackedDeviceID在内部选择中不可见。所以如果我加
WHERE DeviceID = tblTrackedUsers.TrackedDeviceID
正上方
GROUP BY DeviceID
这是行不通的。
我该如何优化此查询？
仅涉及相关字段的表的结构：
tblGPSDevices：
ID |电邮|已验证|已启用
tbl位置：
ID |设备ID |纬度|隆|半径| CreationTimeStamp
tblTrackedUsers：
ID | TrackerDeviceID | TrackedDeviceID |已验证
tblLocations.DeviceID，tblTrackedUsers.TrackerDeviceID和tblTrackedUsers.TrackedDeviceID是指向tblGPSDevices.ID的外键
该查询的作用：
该查询应返回用户正在跟踪的tblGPSDevices中的所有设备，以及tblLocations中它们的最后位置。确定用户正在跟踪哪些设备的方法由tblTrackedUsers确定：从tblTrackedUsers中选择TrackedDeviceID，其中TrackerDeviceID = some_value
最佳答案
我确实找到了答案，并将发布以供将来参考。从2秒开始，查询速度加快。至0.0179秒那是巨大的收获。
关键是要在其中添加一个内部选择：
SELECT DeviceID, MAX(CreationTimeStamp) AS CreationTimeStamp, MAX(ID) AS ID
FROM tblLocations
GROUP BY DeviceID
为了避免全表扫描，因为我们只对tblTrackedUsers.TrackedDeviceID =的DeviceID感兴趣。现在，此选择看起来像这样：
SELECT C.DeviceID, MAX(C.CreationTimeStamp) AS CreationTimeStamp, MAX(C.ID) AS ID
FROM tblLocations C
INNER JOIN (
SELECT ID, TrackerDeviceID, TrackedDeviceID, TrackedName, AccessCode, Validated FROM tblAllowedUsers WHERE TrackerDeviceID = 1 AND Validated=0x01
) AS D ON D.TrackedDeviceID = C.DeviceID
GROUP BY DeviceID
现在是全部选择：
SELECT tblGPSDevices.Email, tblLoc.Lat, tblLoc.Lon, tblLoc.Radius, tblLoc.CreationTimeStamp, tblTrackedUsers.ID, tblTrackedUsers.TrackerDeviceID, tblTrackedUsers.TrackedDeviceID
FROM tblTrackedUsers
INNER JOIN tblGPSDevices ON tblTrackedUsers.TrackedDeviceID = tblGPSDevices.ID
LEFT OUTER JOIN (
SELECT A.DeviceID, A.Lat, A.Lon, A.Radius, A.CreationTimeStamp, A.ID
FROM tblLocations A
INNER JOIN (
SELECT C.DeviceID, MAX(C.CreationTimeStamp) AS CreationTimeStamp, MAX(C.ID) AS ID
FROM tblLocations C
INNER JOIN (
SELECT ID, TrackerDeviceID, TrackedDeviceID, TrackedName, AccessCode, Validated FROM tblAllowedUsers WHERE TrackerDeviceID = 1 AND Validated=0x01
) AS D ON D.TrackedDeviceID = C.DeviceID
GROUP BY DeviceID
) AS B ON A.DeviceID = B.DeviceID
AND A.CreationTimeStamp = B.CreationTimeStamp
AND A.ID = B.ID
) AS tblLoc ON tblLoc.DeviceID = tblGPSDevices.ID
WHERE tblGPSDevices.Validated = 0x01
AND tblGPSDevices.Enabled = 0x01
AND tblTrackedUsers.Validated = 0x01
AND tblTrackedUsers.TrackerDeviceID = 1
ORDER BY tblTrackedUsers.ID;