Menu
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.
424
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement.
The INSERT ... VALUES , INSERT ... VALUES ROW() , and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. You can also use INSERT ... TABLE in MySQL 8.0.
The memory table type/storage engine creates tables, which will be stored in our memory. It is also known as HEAP before MySQL version 4.1. This table type is faster than MyISAM because it uses hash indexes that retrieve results faster.
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
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
