Watching a table for change in MySQL?

Question

Is there a better way to watch for new entries in a table besides selecting from it every n ticks of time or something like that?

I have a table that an external program updates very often, and clients can watch for this new data as it arrive, how can I make that without having to set a fixed period of repeatable select statements?

Answer 1

In MySQL there's no best way than to poll (you create a specific table to simplify the polling though), in other databases you can have triggers that have impact outside the database. In MySQL triggers can only do stuff inside the database itself (for instance, populating the helper table).

Answer 2

Another similar approach would be to add

add column Last_Modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP  

to each table and preface your select queries to compare the last request date/time with the max(Last_Modified).

Databases are typically pull sources and not push so you'll still need to programmatically probe for changes no matter what.