How to speed up mysql query using cache
(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)