Re: How to check for new records in mysql database every minute

You could put a trigger on the table.
See MySql trigger docs here...
http://dev.mysql.com/doc/maxdb/en/a7/41ee0b605911d3a98800a0c9449261/content.htm
You could have this trigger insert into another table for each row that is inserted, updated or deleted.
You would then write a perl program to select from this new 'transaction' table.
This will keep you from doing full table scans off the actual table trying to determine the new records and gives you a way of knowing the records that were deleted.

Update:
Ok, dragonchild points out that triggers are not prime time yet. Can you change the API that does the Insert/Update/Deletes to the table? If you can then you can simulate a trigger by populating a 'transaction' table there. Deletes are the tricky part of your requirements and will require some kind of transaction queueing to manage.

Comment on Re: How to check for new records in mysql database every minute

Replies are listed 'Best First'.
Re^2: How to check for new records in mysql database every minute by dragonchild (Archbishop) on Oct 05, 2004 at 21:39 UTC
Triggers are not production-ready yet. They're in the 5.x series, which is still under new development. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^3: How to check for new records in mysql database every minute by mifflin (Curate) on Oct 05, 2004 at 21:41 UTC
well that sucks. I guess I've been spoiled doing all my db development in Oracle	[reply]
Re^4: How to check for new records in mysql database every minute by dragonchild (Archbishop) on Oct 05, 2004 at 21:45 UTC
Triggers should be production-ready by the end of Q12005, most likely. I'm basing this on prior experience with how fast MySQL AB generally does its new development. Clustering should also be ready around that time, which will be very cool. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]