我有一个简单的问题:
SELECT Description, Temp1, Temp2, SensorData.DateAndTime
FROM SensorData
INNER Join Sensors on Sensors.ChipId=SensorData.RockID
WHERE SensorData.Id in (Select max(Id) LastRecord
From SensorData
Group by RockId)
ORDER BY DhtTemp;
需要3秒钟。如果删除内部select查询并将其替换为查询结果,则需要0.1秒。如果我单独运行内部查询,则需要0.1秒。如果我移除传感器的连接,它将在0.5秒内运行(这很好),有什么建议吗?我在id列上有int索引。
最佳答案
ChipId
表中的Sensors
用于联接条件,而内部选择查询中的RockId
为列名,是否尝试对此列进行索引?
此外,请尝试以下查询:
SELECT Description, Temp1, Temp2, myData.DateAndTime
FROM
(SELECT *,max(id) as mymax FROM SensorData
Group by RockId
Order by id desc
) as myData
INNER Join Sensors on Sensors.ChipId= myData.RockID
WHERE SensorData.Id = mydata.mymax
ORDER BY DhtTemp;