How to speed up mysql query using cache I'm using the following query to create a leaderboard of top 10 people in this week.

SELECT 
users.id, 
concat(users.first_name, ' ', users.last_name) as name, 
users.picture_url, 
MAX(rounds.speed) as speed, 
AVG(rounds.accuracy) as accuracy, 
SUM(rounds.score) as score, 
users.level 
FROM users INNER JOIN rounds ON users.email=rounds.email 
WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE() 
GROUP BY users.id  
ORDER BY score DESC, speed DESC 
LIMIT 10


Currently, it takes around 6 seconds to run this query. The table 'rounds' contain around 3000 rows. Soon it will be much bigger. So once the user opens the leaderboard it takes more than 6 seconds to load!

Is there any way of caching or improving the query so that it loads faster?

Database: MySQL Backend: PHP5 Framework: Codeigniter
(1) Consider designing the schema so that you join on users.id, not users.email.  That will lead to a better way to formulate the query -- by computing the aggregates before joining to users.

(2) Don't hide indexed columns inside functions; the index cannot be used.  Instead of

WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK
                                AND CURDATE() 


use

WHERE dateTime &gt;= CURDATE() - INTERVAL 1 WEEK
  AND dateTime  &lt; CURDATE()


(3) Composite indexes:

INDEX(email, dateTime)   -- after doing (2)
INDEX(dateTime)  -- after doing both (1) and (2)