I have a mySql database and produce webpages through use of php.
I have a database that in simplified form collects data of the form:
date|name|score which is unique for
From this database we create a table that produces the top 12 scores per player per year. This query takes a while to run and I was thinking of how one can speed up the process. My intuition tells me that the speed is related to the size of the database.
My thoughts are:
- Somehow split the database into past years and this year. Sort of like an archive. Then I will have two databases.
- Somehow create a temporary table of just this year's scores and then run the top 12 join query on this table. If so, then how should one go about doing this?
So my question is: which of these two options is best, or even, are there better options that I haven't thought of?