Let's say I have a table of employees and I want to record their monthly salary inside a table and I only want the latest 12 rows to be there.
I know I can do something like this every time I want to insert a salary record:
INSERT INTO salary (employee_id, amount, timestamp) VALUES (id, amount, current timestamp); DELETE FROM salary WHERE employee_id=id ORDER BY timestamp ASC OFFSET 12;
But is there a better approach?
Update the most ancient record instead of insert/delete. The amount of records will be constant, and you'll store the most recent records.
That seems like it could be really inefficient? As I am developing an application, I need to check if there are 12 rows or not, if there isn't then insert, if there is, I need to update all the rows in the table and move the data on the bottom row and update the first row - that'll take a lot of queries. I need to use automation, I need a better approach to improve performance.
I tried to read from the official reference and search the google to find clear details about
INSERT INTO ON DUPLICATE and
REPLACE, but unfortunately I can't understand how they could help in my situation.
Can someone give me an example approach please?