So, for my Survival Games Minecraft plugin (think Hunger Games) I have a web based stats system. It all works perfect, the only issue is when I get tons of data in the databases, the sql queries get extremely slow. I was wondering if anyone could help with optimizations on them. Here is the first query. Im assuming its all the sum()'s but im not sure of a better way to do this.
Code:
$sql = 'SELECT `player`,sum(kills) as tkills ,sum(death) as tdeath,sum(time) as ttime, sum(points) as total_points , sum(position) as tpos, sum(position = 1) as wins, count(*) as tgames, sum(position)/count(*) as avgpos,sum(kills)/sum(death) as kd FROM '.$dbprefix.'playerstats GROUP BY player ORDER BY '.$sort.' desc LIMIT '.($p*$per_page).','.$per_page;
Now that works fine until you get millions of results in the database, and it gets slow. to see more of what this does http://mc-sg.org/stats
You need to think about this more fundamentally. There's no reason that you should be re-generating this list every time the page loads, for starters. It should be generated once every N minutes or hours, and cached either in a database or even as a static page. It's multi-page? That's fine, cache out a lot of the first X pages, and assume that the later pages aren't going to get hit much. GROUP BY is your biggest problem there.
How would i go about this exactly? keep in mind this system is deployed to thousands of servers running my plugin so there will be many different scenarios. I could keep the cache in a a separate table I guess.
Also, you say that the group by is the biggest problem. Is there any other way to do that?
Most SQL servers will also have ways to analyze a query for performance tuning - even SQLite has that. Look at the manual for whatever setup you are using/support and see what it offers.
Register to Join the Conversation
Have your own thoughts to add to this or any other topic? Want to ask a question, offer a suggestion, share your own programs and projects, upload a file to the file archives, get help with calculator and computer programming, or simply chat with like-minded coders and tech and calculator enthusiasts via the site-wide AJAX SAX widget? Registration for a free Cemetech account only takes a minute.
»
Go to Registration page
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
