What's the fastest way to poll a MySQL table for new rows?

php mysql triggers polling
HyderA picture HyderA · Sep 8, 2010 · Viewed 8.2k times · Source

My application needs to poll a MySQL database for new rows. Every time new rows are added, they should be retrieved. I was thinking of creating a trigger to place references to new rows on a separate table. The original table has over 300,000 rows.

The application is built in PHP.

Some good answers, i think the question deserves a bounty.

Answer

TFD picture TFD · Sep 16, 2010

For external applications I find using a TimeStamp column is a more robust method that is independent of auto id and other primary key issues

Add columns to the tables such as:

insertedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP

or to track inserts and updates

updatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In the external application all you need to do is track the last timestamp when you did a poll. Then select from that timestamp forward on all the relevant tables. In large tables you may need to index the timestamp column

Related questions

Invoking a PHP script from a MySQL trigger

Is there any way how to invoke a PHP page / function when a record is inserted to a MySQL database table? We don't have control over the record insertion procedure. Is there a trigger mechanism which can call a PHP …

Read More
Notification system using php and mysql

I wanted to implement a notification system for our school, it's a php/mysql webapp that is not opened for public, so it doesn't receive much traffic. "daily 500-1000 visitor". 1. My initial approach was using MYSQL triggers: I used a …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More