CODEWITHSUNDEEP
mysqlsqlperformancedatabase-performance
darkheir
darkheir

Reputation: 8950

Optimize sql request for statistics

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!

Upvotes: 0

Views: 72

Answers (2)

RustamIS
RustamIS

Reputation: 697

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.

Upvotes: 1

Francois
Francois

Reputation: 10978

For archived statistics, you can create tables that store rankings that won't move (last year, last month, last day). Try to calculated as much as possible statistics in such tables, put indexes on id_user, date, type_of_ranking...

Try to limit as much as possible subqueries.

Upvotes: 1

Related Questions