How to speed up mysql query using cache

Question

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

• jcolebrand: I would start with using precomputed values on the where clause instead of doing the calculation every time, but I don't know if the query compiler/optimizer can pick that out ahead of time. Sometimes it's easier to just write simpler code. Next, you should consider getting the users before doing the max/avg/sum on every user, so you only run those ops on the ones you need. Think about creating this in two or three batches, and you will find that it runs much much faster.
• The Coder: create statement..? indexes..?
• Rick James: @jcolebrand - I thought such date expressions were precomputed in the WHERE clause. Can't pick 10 users until the computations on rounds is done.

Answer 1

(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 >= CURDATE() - INTERVAL 1 WEEK
 AND dateTime < CURDATE()

(3) Composite indexes:

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