A friend and I put together a MySQL php website where users can make predictions on sports events.
My question is regarding our "rankings" page which displays the list of members who have made the best predictions. The way we had it initially was each time a user loaded that page the server would calculate all the predictions from all the users, then displayed the top ones. Unfortunately, even with a small group alpha testing it, the loading of this page quickly became slower as the number of predictions grew.
Ideally speaking, we would like to have filters on this page, for each different sport, league and time frames, and create a dynamic rankings page, but we're stuck on finding an efficient way to structure our database that won't use up too much server resources.
Any ideas on what a better way could be is greatly appreciate. Thanks!
You could use "materialized views" to calculate the score. A materialized view is a view (thus some query), but where the results are stored (in memory) and updated accordingly:
http://www.fromdual.com/mysql-materialized-views
One can expect that the difference (in the number of (correct) predictions) will not change dramatically between two page renderings. Thus that the number of calculations will be quite low.