database SELECT query problem

Question

I am new to database, i faced a very strange problem recently:

1. I create a database as a leaderboard server for my game where it holds the player names and scores. I created a php scipt to query the database, and the game will open the php url with GET arguments.

2. My submit score function works perfectly. I use phpadmin to check. Everytime I submit it will update the database correctly.

3. The problem is retrieving the leaderboard, my game will retrieve the leaderboard every 10 secs. It succeeds everytime. But the scores it gets are always not the lastest records in database, all are previous records. And after some hours, it can get newer data.

Below are my troubleshoots: 1 I didn't include "commit" in my submit score php script. So I manully do commit, but game still can't get the lastest data.

1. I try to manullay print the data in web browser by entering php address with GET arguments. It shows previous data same as in my game. So the problem is not with the game. I also mannualy enter the sql commands in phpadmin, it will return the lastetes data. So i assure the problem is with my php?

php script:

$query = "SELECT * FROM `space_scores` ORDER by `score` DESC";
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    $num_results = mysql_num_rows($result); 


    for($i = 0; ($i < $lines)&&($i<$num_results); $i++)
    {
         $row = mysql_fetch_array($result);
         echo $i+1 . "," . $row['name'] . "," . $row['score'] . "|";
    }

Answer 1

You need to turn off query caching

set session query_cache_type=0;

See this link for details:

http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

Answer 2

Your php server is caching the sql query to avoid repetetive querying and reduce load

Turn it off by

set session query_cache_type=0;

in the start of the php script for select.

Also, running a db query every 10 seconds is not a good idea. When you scale up to 1000 users (suppose) you will have at least 100 requests per second which is not very good for your server.

Consider running the php script which querys the db and writes the results to a xml or comma delimited file every 1 minute or so by setting up a cron job and get this file from the server every 10 seconds instead of the php