Optimize sql request for statistics

Question

I would like to perform some request in mysql that i know will be really slow:

I have 3 tables:

Users:

id, username, email

Question:

id, date, question

Answer

id_question, id_user, response, score

And i would like to do some statistics like the top X users with the best score (sum of all the scores) for all time or for a given amount of time (last month for example). Or it could be users between the 100th and the 110th range

I will have thousands of users and hundred of questions so the requests could be very long since I'll need to order by sum of scores, limit to a given range and sometimes only select some questions depending on the date, ...

I would like to know if there are some methods to optimize the requests!

Answer 1

If you have a lot of data no other choices, Only you can optimize it with creating new table that will somehow summarizes data in every day/week or month. Maybe summarizes scores by each week for users and stamps by that weeks date, or by month. As the range of summing longer as much your query works fast.