mysql响应时间长

6rvt4ljy  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(461)

我有一个有效的mysql查询,它从数据库中输入的每个社区中选择表的最新占用率,但它似乎在扫描整个数据库的条目,因为查找时间大约需要3-4秒。
根据下面查询中提供的详细信息,有人能为我提供一种更快/更好的方法来查找每个社区的最新时间戳字段吗我需要查询来选择每个输入的社区,并带有最新的时间戳,但是每个选择的社区的限制应该是1(这意味着名为“test community”的社区可能有数百个提交,但是我需要选择最新输入的时间戳,以及表中输入的每个社区的相同选择)

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
vawmfj5a

vawmfj5a1#

根据我的经验,相关子查询的性能通常很差;请尝试以下操作:

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
hsgswve4

hsgswve42#

你的问题很好。对于这个查询(只重写了一点):

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) (可能已经存在)
相关子查询本身不是问题。

相关问题