Polling alternative

[Chris92]

Is there an alternative to polling a MySQL database?

 

Basically I have a PHP script that polls a table every half a second. This script then returns a value to a client that is doing a reverse-ajax request on the server.

 

Is there a different technique that can be used, so MySQL doesn't return until there is an update rather than polling for an update.

 

Thanks in advance,

Chris

[trq]

You might find this.... http://www.subbu.org/blog/2006/04/dissecting-ajax-server-push interesting.

[Chris92]

Wasn't really what I was looking for. It just explains reverse-ajax. What I'm looking is more of a MySQL to PHP push technique. Not PHP to JavaScript.

 

What my PHP script does is this:

 

$query = mysql_query("SELECT * FROM `table` WHERE `time` > ". (int)$_SESSION['last_package_time'] ."");
$rows = mysql_num_rows($query);

while( $rows == 0 )
{
    $query = mysql_query("SELECT * FROM `table` WHERE `time` > ". (int)$_SESSION['last_package_time'] ."");
    $rows = mysql_num_rows($query);
    set_time_limit(1);
    usleep(250000);
}

echo json_encode(mysql_fetch_assoc($query));

 

Which as you can see is rather straining on the MySQL server. There's bound to be a way for MySQL to not return a value until the query has a value.

[trq]

How are you accessing this script though? Via HTTP?

[Chris92]

Yeah.

[trq]

Which as you can see is rather straining on the MySQL server. There's bound to be a way for MySQL to not return a value until the query has a value.

 

Even if there was (which there isn't) you would still need to query the database to find out (which is actually what you are doing).

[Chris92]

So the answer is no? The only way is polling?

[trq]

Think about how HTTP works.

[PFMaBiSmAd]

So, what exactly is your application dong that would require this and where is the data being inserted from that you want to retrieve in the way?

 

You would generally use a shared memory/disk cache system when you need frequent polling to get current data.

[Chris92]

It's for a web game. The thing is the game has to be accessible by an API, for cross platform compatibility, so I'm storing everything in a database rather than a cache system.

 

The web application works like any other typical web system would. It sends data through one Ajax request and gets it through another.

 

The getting part uses reverse-ajax. So the client doesn't poll the server when there's no need for it. Then the server runs a PHP script that polls the MySQL server for updates which then returns the new values to the HTTP server which then returns data to the client.

 

I have read up on PostgreSQL and it has listen and notify functionality. Does MySQL not come with the same?

[jdavidbakr]

If you have caching on your MySQL server then you're probably not going to actually use much resources when polling the database.