In my game Blue Defense, I've got online high scores. the way I'm doing it is via HTTP requests on PHP pages, and then the php goes and queries a bunch of data from my database through SQL. Due to Blue Defense being fairly popular, I am having server troubles - meaning that my host reports to me fairly regularly that I'm going over my CPU quota, and that locks *everyone* out for 60 seconds and allows no HTTP requests to get served up. To make matters worse, I have no idea if my PHP is sucking up too much time, or if my queries are the bottleneck, or if each request is sending across too much data, etc etc etc. So my question is: What is the best way to access this data in the most efficient way possible? What other best practices can I build in to both my database structure and general infrastructure (client/server implementations)? examples of better practices: 1) is there a way to access my server's SQL database directly, or am I stuck with this PHP-SQL intermediary solution? 2) is there a way to speed up my php that I'm just not thinking about? 3) What ways can I optimize the database itself? (Proper indices, obviously, Views, but what else?) 4) ohgodhelpmebluedefenseuserscantgetonlinehighscoresrightnowwwwaaaaahhhh
1) if there is, it'd probably be a pain from your app point of view to communicate with it, and might leave your database a little unsecure for abuse... 2) maybe use microtime at various points in the php to print out how long different sections of the code are taking, work out what takes the longest and optimise that a bit. PHP is pretty good at the speed thing though, it's hard to squeeze speed out of the actual code, it's usually the design patterns fault if the bottleneck is the query (which it probably is) you could try reducing the data you pull out of it to just the bits of data you require, as in don't fetch the whole row, just "name" and "score" (or whatever you need Reduce the sizes of the data in the database if you can, if you have a blob for the name, change it to 20 chars etc I *think* phpmyadmin (if you have it availible or can install it) can tell you how to optimise the structure... If you make more than one query (say for #1-10) you can usually squeeze them all into one query... If you often just pull out the top 10 you could cache that into a file that you can just echo/print out and save an SQL query, (just update the file if you write a new score that changes the top 10). Obviously you'll have to test to see if file access is faster than the SQL query and disable access for any pirated copies
For optimizing the DB, you can use a profiler to tell where most of the processing is happening in any given query: http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html This probably doesn't help as much at this point in the game, but I thought this guy had an interesting approach to implementing high scores: http://silentmac.com/?p=151 So with his approach, hopefully most of the time you're just serving up an existing HTML page instead of having to query a whole list of score data every time.
After some testing, it seems that Blue Attack might be hogging up a fair amount of CPU on my server with inefficient queries. However, to be scalable, I definitely want to implement many of these ideas. The silentmac guy touched on something I'm very interested in, which is preprocessing - instead of doing a DB query every time for a mostly-static high scores list (which then involves MySQL and PHP marshalling the data across the boundary, then my PHP script re-marshalling the data into a form that the iPhone will read), I'll detect when a new score will actually affect the list, then re-process the actual page returned to the iPhone. that way, the marshalling/processing only happens when necessary (maybe tens of times per day, less as time goes on), rather than the tens of thousands of times it's currently happening. Thanks for your input, guys. and I do have PHPMyAdmin, and I've looked up the optimization feature. I didn't know that SQL had a built-in analysis query tool.
The other nice side effect is that it makes viewing the high scores inside your iphone app trivial.. one UIWebView and you're set. Keep us posted if you go that route, I have yet to implement high scores myself.
