title: Optimising this mysql query
body: <p>I'm looking to speed up this query somehow. Possibly to the point where its acceptable to run it on a low traffic site. It takes 15+ seconds which is way too long. </p>
  
  <p>Currently there are 13k rows in the table, which is approx 1 months data, however i'm expecting that monthly data to double once the site is in production. The aim is to select the top 10 gains for the last week.</p>
  
  <p>there are two tables, users table and the tracker table, in the tracker table there are multiple rows for each user, at least 8 per day,  per user.</p>
  
  <p>The aim of the query is to take the latest row for each user, subtract from that a value from a row inserted 1 week ago to get the amount of xp they have gained and select the top 10 highest gainers.</p>
  
  <p>Table schema (which im sure could be improved too)</p>
  
  <p>users table</p>
  
  <pre><code>id int(11) primary
  rsn varchar(12) unique
  joined timestamp
  disabled bool
  </code></pre>
  
  <p>tracker table</p>
  
  <pre><code>id int(11) primary
  user_id int(11) index /* Associated with the id in the users table */
  timestamp timestamp
  overall_rank int(11)
  overall_level int(4)
  overall_exp int(10)
  </code></pre>
  
  <p>And the query.</p>
  
  <pre><code>SELECT  `users`.`rsn` ,  `tracker`.`timestamp` , @uid :=  `tracker`.`user_id` , (
      SELECT  `overall_exp` 
      FROM  `tracker` 
      WHERE  `user_id` = @uid 
      ORDER BY  `timestamp` DESC 
      LIMIT 1
  ) - (
      SELECT  `overall_exp` 
      FROM  `tracker` 
      WHERE  `timestamp` &gt;= SUBDATE( NOW( ) , INTERVAL 1 WEEK ) 
      AND  `user_id` = @uid 
      ORDER BY  `timestamp` ASC 
      LIMIT 1 ) AS  `gained_exp` 
  FROM  `tracker` 
  JOIN  `users` ON  `tracker`.`user_id` =  `users`.`id` 
  ORDER BY  `gained_exp` DESC 
  LIMIT 10
  </code></pre>
  
  <p>Explain output</p>
  
  <pre><code>+----+----------------------+---------+-------+---------------+---------+---------+------------------+-------+----------------------------------------------+
  | id | select_type          | table   | type  | possible_keys | key     | key_len | ref              | rows  | Extra                                        |
  +----+----------------------+---------+-------+---------------+---------+---------+------------------+-------+----------------------------------------------+
  |  1 | PRIMARY              | users   | index | PRIMARY       | rsn     | 14      | NULL             |    71 | Using index; Using temporary; Using filesort |
  |  1 | PRIMARY              | tracker | ref   | user_id       | user_id | 4       | surreal.users.id |   103 |                                              |
  |  3 | UNCACHEABLE SUBQUERY | tracker | ALL   | NULL          | NULL    | NULL    | NULL             | 11752 | Using where; Using filesort                  |
  |  2 | UNCACHEABLE SUBQUERY | tracker | ALL   | NULL          | NULL    | NULL    | NULL             | 11752 | Using where; Using filesort                  |
  +----+----------------------+---------+-------+---------------+---------+---------+------------------+-------+----------------------------------------------+
  </code></pre>