Looking for SQL Help

[Karawasa]

We run a set of queries to generate "summary" tables for the leaderboards. This allows read access at lightning speed because we are only selecting from a subsection of the total. The problem (or future problem) is that the generation of these tables is a bit slow. I'm hoping someone better at SQL than me can help optimize them.

 

http://pastebin.com/UZMCpCc9

[GoatAss]

If still needed, I can ask a friend in the SQL department. He does pgsql and oracledb mostly.

[Bruno Martins]

On 09/03/2016 at 10:51 AM, Karawasa said:

We run a set of queries to generate "summary" tables for the leaderboards. This allows read access at lightning speed because we are only selecting from a subsection of the total. The problem (or future problem) is that the generation of these tables is a bit slow. I'm hoping someone better at SQL than me can help optimize them.

 

http://pastebin.com/UZMCpCc9

 

This is not a huge advice but it might help a little bit.

I should the replace php variables like this : " WHERE m.version = '".VERSION."'  " to something like : " WHERE m.version = :version  " and set the ":version" value in the code later before you execute. The DB will get more performance because this will your query is always the same. in your approach you change the query everytime so the Db need to do all the parse again, in my example we just change the value not the query and this can save same processing time.

Also if you need to keep historic data from your tables, my adivice is not create something new everytime, just create a flag like FL_VISIBLE to ignore the old version registers.After that you might create index that will help your query to work faster.Once you do the same query over and over again you should create an index in the fields that you use there, so it will become faster.

 

I hope that this help you to improve your results.

 

Kind Regards,