How do you create a threshold or limit a mysql table?

Question

I have a table in which I would like to have only the last 20 records , because the table adds a row every 0.5 seconds.

How to do this when querying a database (mysql).

Should I add a new row and delete the oldest one when I want to push a new value?

Answer 1

You can create a view . To ensure recent ordering, enter a column with a timestamp for the order (or use a 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, remember to “clean” the base table from time to time.

Answer 2

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

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

Answer 3

Yes, you need to delete all rows older than the 20th oldest row every time you insert, if the table should always only have the last 20 rows.

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

The best solution would be to use a view for the query, do not delete it every time you insert, but once a day during downtime it starts a deletion that leaves only the last 20 lines.

Just make sure the index column is indexed.

Answer 4

Another approach to keeping the MySQL table the same size might be:

• Count the number of input records before inserting with PHP, i.e. $ records_to_add = 20;
• Delete the oldest records $ records_to_add from table A. (Remember to ORDER and LIMIT your deletion request.)
• INSERT new entries to the table. (It would be better to do this with a single query rather than an INSERT INTO query in a loop.)

The table remains the same in size. Hope this helps!