Efficiently using SQL data over multiple page loads
Posted: Wed Aug 26, 2009 8:26 pm
Note: This relates mostly to PHP+MySQL game development, but it can definitely be applied toward a large number of PHP+MySQL websites.
A quick backstory
In 2007-2008, I wrote a large game in PHP+MySQL, and it was fairly successful. However, its downfall was this: my code was largely unoptimized. As a result, I had trouble finding an affordable server that could withstand the heavy load the website received. Upon some investigation, I quickly found that MySQL services were taking the large majority of the server's processing time (as opposed to the PHP service). It seemed my MySQL calls were either too large or too frequent-- perhaps a mixture of both. I didn't have any calls that were particularly hefty-- it was just that I had a dozen or more players loading pages every few seconds, and each page load contained at least a few MySQL queries: a mandatory SELECT query to get the player's character data (which consists of something like 30 or 40 fields), a SELECT query or more to get information on which location the player is in and possibly something like which monster the player is facing, and then often an UPDATE query to update the player's data based upon what happened in that page load. When all this was added up, the server was dedicating a lot of time to processing MySQL queries.
It's my goal now to revive this project and optimize the code, reducing the MySQL query processing load and, as a result, allowing me to host the game on a cheap(er) server.
I've contemplated ways that I can accomplish this optimization. I'm going to focus today on one optimization method I'm considering, but if anyone has more/better ideas of ways to cut down on the MySQL load, I'd love to hear them too.
The main optimization theory I'm posting about
Here's what I'm hoping to accomplish. I'm not sure how well it will work out (will it cut down a significant or insignificant amount of load? are there any problems I might unexpectedly run into using this method?), so that's why I'm posting here (and also to get more ideas on other ways to optimize the SQL queries).
My plan is to essentially eliminate the "mandatory" query that I mentioned earlier: the query that SELECTs the player's character data at the start of every page load. The way I intend to do this is to call that query only right when the player logs in, and have that data saved within session data. I assume that accessing session variables is much faster than querying the same data from a database (if not, by all means let me know). At that point, whenever a page load needs to know something about the character's stats/location/name/etc., it'll request it from the session variable array instead of from the SQL server. Of course, whenever that session data is updated with new information (ex: the player levels up, so the "level" variable's value is increased), an UPDATE query will serve to ensure that the SQL data always remains consistent.
Is this practical? Efficient? Are there any pitfalls to watch out for? Is there a better alternative to solving my problem?
A quick backstory
In 2007-2008, I wrote a large game in PHP+MySQL, and it was fairly successful. However, its downfall was this: my code was largely unoptimized. As a result, I had trouble finding an affordable server that could withstand the heavy load the website received. Upon some investigation, I quickly found that MySQL services were taking the large majority of the server's processing time (as opposed to the PHP service). It seemed my MySQL calls were either too large or too frequent-- perhaps a mixture of both. I didn't have any calls that were particularly hefty-- it was just that I had a dozen or more players loading pages every few seconds, and each page load contained at least a few MySQL queries: a mandatory SELECT query to get the player's character data (which consists of something like 30 or 40 fields), a SELECT query or more to get information on which location the player is in and possibly something like which monster the player is facing, and then often an UPDATE query to update the player's data based upon what happened in that page load. When all this was added up, the server was dedicating a lot of time to processing MySQL queries.
It's my goal now to revive this project and optimize the code, reducing the MySQL query processing load and, as a result, allowing me to host the game on a cheap(er) server.
I've contemplated ways that I can accomplish this optimization. I'm going to focus today on one optimization method I'm considering, but if anyone has more/better ideas of ways to cut down on the MySQL load, I'd love to hear them too.
The main optimization theory I'm posting about
Here's what I'm hoping to accomplish. I'm not sure how well it will work out (will it cut down a significant or insignificant amount of load? are there any problems I might unexpectedly run into using this method?), so that's why I'm posting here (and also to get more ideas on other ways to optimize the SQL queries).
My plan is to essentially eliminate the "mandatory" query that I mentioned earlier: the query that SELECTs the player's character data at the start of every page load. The way I intend to do this is to call that query only right when the player logs in, and have that data saved within session data. I assume that accessing session variables is much faster than querying the same data from a database (if not, by all means let me know). At that point, whenever a page load needs to know something about the character's stats/location/name/etc., it'll request it from the session variable array instead of from the SQL server. Of course, whenever that session data is updated with new information (ex: the player levels up, so the "level" variable's value is increased), an UPDATE query will serve to ensure that the SQL data always remains consistent.
Is this practical? Efficient? Are there any pitfalls to watch out for? Is there a better alternative to solving my problem?