MySQL 响应时间长

Question

我有一个有效的MySQL查询，它从我的数据库中输入的每个社区中选择表的最新占用百分比，但它似乎正在扫描整个数据库中的条目，因为查找时间大约需要3-4秒。

通过以下查询中提供的详细信息，有人可以为我提供更快/更好的方法来查找每个社区的最新时间戳字段吗？ - 我需要查询来选择输入的每个社区，并带有最新的时间戳，但每个选择社区的限制应该是 1(这意味着名为"测试社区"的社区可能有数百个提交，但我需要选择最新输入的时间戳， 以及表中输入的每个社区的相同选择(

SELECT t1.reportID, t1.communityID, t1.region, t1.percentOccupied,  
t1.TIMESTAMP, Communities.fullName
FROM NightlyReports t1 
INNER JOIN Communities On t1.communityID = Communities.communityID
WHERE t1.TIMESTAMP = ( SELECT MAX( TIMESTAMP ) FROM NightlyReports WHERE 
t1.communityID = NightlyReports.communityID ) 
AND t1.region =  'GA' ORDER BY percentOccupied DESC

Answer 1

根据我的经验，相关子查询的性能通常很差; 试试这个：

SELECT t1.reportID, t1.communityID, t1.region, t1.percentOccupied
, t1.TIMESTAMP, Communities.fullName
FROM NightlyReports AS t1 
INNER JOIN Communities ON t1.communityID = Communities.communityID
INNER JOIN (
SELECT communityID, MAX( TIMESTAMP ) AS lastTimestamp
FROM NightlyReports 
WHERE region = 'GA'
GROUP BY communityID
) AS lastReports ON t1.communityID = lastReports.communityID
AND t1.TIMESTAMP = lastReports.lastTimestamp
WHERE t1.region =  'GA' 
ORDER BY percentOccupied DESC

Answer 2

您的查询很好。 对于此查询(仅重写了一点(：

SELECT nr.reportID, nr.communityID, nr.region, nr.percentOccupied,  
nr.TIMESTAMP, c.fullName
FROM NightlyReports nr INNER JOIN
Communities c
ON nr.communityID = c.communityID
WHERE nr.TIMESTAMP = (SELECT MAX(nr2.TIMESTAMP)
FROM NightlyReports nr2
WHERE nr.communityID = nr2.communityID
) AND
nr.region =  'GA'
ORDER BY percentOccupied DESC;

您希望索引：

• NightlyReports(region, timestamp, communityid)
• NightlyReports(communityid, timestamp)
• Communities(communityID)(这可能已经存在(

相关子查询本身不是问题。