Sign in to follow this  
Followers 0
Karawasa

Looking for SQL Help

3 posts in this topic

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

Share this post


Link to post
Share on other sites
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,

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0