Re: Query takes time to execute
You haven't give enough information to give a 100% accurate result. The structure of the table and an EXPLAIN done on the query would help a lot.
The only thing I see that I think might be able to help would be an INDEX on the date column. How selective is that lookup?
For example, what is the difference between:
> SELECT count(*) FROM game_result;
> SELECT count(*) FROM game_result WHERE date >= SUBDATE(NOW(), INTERVAL 1 DAY);
Even with the index, if there are quite a few results from the second one, then it will still be slow. If you have 300,000 items left, that is 300,000 items mysql will need to SUM up, and then sort by, which can take a bit of time unfortunately.
This would be an excellent canidate for a cache of some sort, either on the client side, or on the MySQL side. By that I mean rather than running this query live all of the time, store the results somewhere and then refresh the results every 15 minutes or the like. That will releave a lot of the stress from doing this query, which will still be quite slow for a webpage, even if it took 5-10 seconds instead.
Hope that helps some,