using php to monitor mysql changes **WITHOUT** polling

Question

I have seen many questions similar to the one I asked. A few of them said that its ok to poll, and that doesn't affect the database performance significantly, and some are still unanswered. What I want to know is this :

• Can I do something similar to ASP.NET's sqldependency, which helps to find out when changes are made to the database WITHOUT polling.
• I know polling is ok to do, might not affect database performance, but I need to know the update as soon as it is made, so I feel I need something better than just polling every 1 second.
• As an add on point, the updates that are detected at the server then have to be pushed (Ajax Push - I'm using Ajax Push Engine) to all the clients who are subscribed to a particular channel.

Thanks & Regards, Thothathri Srinivasan

Answer 1

I think Q4M is exactly what you want http://q4m.github.com/tutorial.html

Answer 2

I like Alfred's pubsub idea. I would think about the database as a subscriber to the insert events, not as the canonical version. Maybe this isn't possible with your existing codebase. You might also be able to monitor the binary logs on the database server to watch changes come in and push this information to the subscribers.

Answer 3

You can use a server push with an implementation like websockets or comet.

Answer 4

Redis PubSub

I hoped triggers could help you out, but after a little examination I (highly) have my doubts.

You could achieve your goal using Redis pubsub. Redis is an extremely fast data structure server. Publish the database changes to a channel and let the interested clients detect the changes by subscribing to that same channel(in real-time).

Downloading/Installing Redis is really easy and as extra bonus you will also "get an open source, advanced key-value store. It is often referred to as a data structure server since keys can contain strings, hashes, lists, sets and sorted sets."

Installing it is probably the best way, but if you can't, you could use the free http://redistogo.com plan to talk the Redis server with the limitation that you will only get 5MB to store your data and that it will not be persisted to disc.