What is the fastest way to query a MySQL table for new rows?

Question

My application should query the MySQL database for new rows. Each time new lines are added, they should be retrieved. I was thinking of creating a trigger to place links to new rows in a separate table. The source table has over 300,000 rows.

The application is built in PHP.

Some good answers, I think this question deserves generosity.

Answer 1

For external applications, I believe that using the TimeStamp column is a more reliable method, independent of automatic id and other problems with the primary key.

Add columns to tables, such as:

insertedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP 

or to track insertions and updates

 updatedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 

In an external application, all you have to do is keep track of the last timestamp when you conducted the survey. Then select all relevant tables from this timestamp. In large tables, you may need to index the timestamp column.

Answer 2

You can use the following statement to find out if a new table has been inserted into the table:

 select max(id) from table_name 

replacing the primary key name and table name in the above description. Store the max (id) value in a temporary variable and retrieve all new entries between this and the last stored max (id) value. After receiving new entries, set the value of max (id) to the value that you received from the request.

Answer 3

Create a PHP daemon to track the size of the MySQL table, if the size changes the query for new records, if new records are found, perform the following process.

I think there is an active PEAR daemon that you can easily configure to control the size of the MySQL table file and run your script.

Answer 4

Assuming you have an identifier or some other data that is always growing, you should track your php application from the last identifier found.

which will work for most scenarios. If you don’t get to the camp in real time, I don’t think you need more.

Answer 5

I would do something like this. Of course, this assumes that the identifier is an incremental numerical identifier. And the way you save your "current location" in the database is up to you.

 <? $idFile = 'lastID.dat'; if(is_file($idFile)){ $lastSelectedId = (int)file_get_contents($idFile); } else { $lastSelectedId = 0; } $res = mysql_query("select * from table_name where id > {$lastSelectedId}"); while($row = mysql_fetch_assoc($res)){ // Do something with the new rows if($row['id']>$lastSelectedId){ $lastSelectedId = $row['id']; } } file_put_contents($idFile,$lastSelectedId); ?> 

Answer 6

I would agree with TFD's answer to tracking the timestamp in a separate file / table, and then retrieving all the newer rows. The way I do it for a similar application.

Your application, requesting a single line table (or file) to see if the timestamp has been changed from local storage, should not greatly affect performance. Then, fetching new rows from the 300k row table based on the timestamp should be in order, assuming the timestamp is correctly indexed.

However, after reading your question, I was curious if Mysql triggers can make system calls, say, a php script, which will do a heavy lift. Turns out they can using the sys_exec () user- defined function . You can use this to do all kinds of processing by passing inserted row data into it, essentially having instant notification of inserts.

Finally, a word of caution about using triggers to call external applications.

Answer 7

One option would be to use the INSERT INTO SELECT statement. Taking from sentences using timestamps to pull out the last lines, you can do something like ...

 INSERT INTO t2 ( SELECT * FROM t1 WHERE createdts > DATE_SUB(NOW(), INTERVAL 1 HOUR) ); 

This will take all the rows inserted in the previous hour and paste them into table 2. You could run the script for this query and run it every hour (or any interval you need).

This will greatly simplify your PHP script for pulling lines, since you will not need to iterate over all lines. It also eliminates the need to keep track of the last insert identifier.

Fanis solution also sounds as if it might be interesting.

As a note, the selection request in the above insertion can only be adjusted to insert certain fields. If you need only certain fields, you will need to specify them in the insert so ...

 INSERT INTO t2 (field1, field2) ( SELECT field1, field2 FROM t1 WHERE createdts > DATE_SUB(NOW(), INTERVAL 1 HOUR) );