How can I avoid mysql query every page load on RPG game? Any ideas?

Question

Hi, I've been doing RPG game recently and every page load I have to use mysql_query (and this query quite big, takes a few tables with inner join) to take information about player. How can I avoid this query sometimes? Of course I can use cache and make query for example every 30 secs, but it won't renew new information instantly. So, any ideas?

Answer 1

I think you should use Memcache.

Answer 2

If the amount of info is limited, and specific for your player, you can put it in $_SESSION.

Answer 3

If you need new fresh information, there is no way you can avoid making a query.

You can create smart caching, deleting the cache when a new entry has been made, or any other time you need fresh information from the database.

Frameworks does a lot of the work for you nowadays, CakePHP excels in that.

Answer 4

To know if anything changed without making the query again, there's two options:

(1) flush the cache if anything does change, or

(2) keep a last-modified or some other small value that changes when the data changes, and keep it with your cached value to see if it is still up-to-date.

Option (1) requires that you know all points in the code where player info is updated. Option (2) is also difficult if you need to combine several tables, you'll need to compute the maximum of the last-modified times for each of the records you'll use.

In any case, good caching is a difficult problem, and you'll need to balance the time required to manage the cache versus the time saved by not having to execute the query. And also consider the penalty of using stale (i.e. out-of-date) information, often (especially in games) using info that's a few seconds out of date is not that big a deal.

Answer 5

First of all you should take a deep look into the data you are using, chances are you don't really need all the data you are pulling from the database on each page. Once you have it figured out in terms of which pieces of data you need in a specific scenario you can speed up the retrieval significantly by:

• Storing non-sensitive data in a cookie for really fast retrieval (as long as you validate the data to prevent tampering)

• Using memcache as Safraz pointed out

• Using the $_SESSION superglobal

• Denormalizing your database to save on heavy joins if neccessary

• Selecting only the data you really need for a specific page, and selecting additional data as needed.

Using a combination of these techniques, you should be able to significally ease the stress on your db and provide a quicker and more responsive application to your users.

Answer 6

What is the ratio of reads to writes? If you have a lot more reads than writes, then the obvious answer is a write through cache sitting in front of the database. How you implement this will depend on the how big you want to scale (i.e. how many concurrent players) and whether you need high availability (i.e. a cache distributed across multiple physical machines.)

Answer 7

Well if you're using PHP Sessions (and you better be) then put the SQL results in the session and on page load check if the array exists in session, if so, use it, if not, run the query.

Let's say something like:

if ( !is_array($_SESSION['player_data']) )
{
    // the data isn't cached, query the database here and put it in $_SESSION['player_data']
}

Answer 8

Try to denormalize the Database.

If you are using inner joins, put all the data in one table. (the resulting data of the inner join) Keep the other tables for when the user edits his values, but for reading use the table where all the data is.

This will improve the speed as there will be no inner joins, you still have to query the db but if you really don't want to, the only solution I can see is some sort of cache using either memcache, sessions or files