How do you make a threshold or limit mysql table?

Question

Hi, I have a table which i would like only to have the 20 most recent entries, because the table is adding a row every .50 seconds.

How would I accomplish this in querying to the database(mysql).

Should i add a new row and delete the oldest whenever i want to push a new value in?

Answer 1

You could create a view. To guarantee ordering by recentness, introduce a column with a timestamp to order by (or use the primary key when using sequential numbering, such as AUTO_INCREMENT).

CREATE VIEW latest_entries AS 
SELECT ... FROM TABLE foo ORDER BY created_time LIMIT 20;

Also, don't forget to 'clean' the underlying table from time to time.

Answer 2

Yes, you need to delete any rows older than the 20th oldest row every time you insert if you always must have only the latest 20 rows in the table.

You can have a view that returns the latest 20 rows, but your table will still grow if all you do is insert.

The best solution might be to use the view for querying, don't delete every time you insert, but once a day at off time run a delete that leaves only the latest 20 rows.

Just make sure the timestamp column is indexed.

Answer 3

If you really want to purge the 20th row of the table when you insert a new row, you will have to delete the row on insert. The best way is to create a Trigger to do this work for you.

CREATE TRIGGER Deleter AFTER INSERT on YourTable FOR EACH ROW
BEGIN
    Delete from yourTable where ID = (Select max(id) from yourTable);
END;