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 date|name.

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:

  1. Somehow split the database into past years and this year. Sort of like an archive. Then I will have two databases.
  2. 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?

  • 2
    Show us the structure of the table(s) involved and the query that is being used. Don't forget to include the indexes. Also, use EXPLAIN on your query and make sure it is using any indexes that you have. Defining "takes a while to run" would also be helpful. – Dave Apr 22 '18 at 10:25
  • 1
    What size are we talking about here? How many rows does your table contain, how many new rows are expected per year? – Lennart Apr 22 '18 at 10:26
  • 1
    Apart from answering the points raised by others above you might want to look here - Rick James' "rules of thumb" page, partitioning section! It's not a panacea! – Vérace Apr 22 '18 at 10:59
  • Need more info. At least give us the query. – Rick James May 8 '18 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.