MySQL Long Response Time

Question

I have a valid MySQL Query that selects the latest occupancy percentage of a table from each community entered in my DB, but it seems to be scanning the entire DB of entries as the lookup time takes roughly 3-4 seconds.

With the details provided in the query below, can someone provide me with a faster/better way to lookup the latest timestamp field for each community? - I need the query to select every community entered, with the latest timestamp, but the limit for each community selected should be 1 (meaning community named "Test Community" will have possibly hundreds of submissions but I need the latest entered Timestamp selected, along with the same selection for every community entered in the table)

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

In my experience, correlated subqueries often have rather poor performance; try this instead:

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

Your query is fine. For this query (which is rewritten just a bit):

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;

You want indexes on:

• NightlyReports(region, timestamp, communityid)
• NightlyReports(communityid, timestamp)
• Communities(communityID) (this may already exist)

The correlated subquery is not per se a problem.