Mysql – Best way to keep the last N rows by a certain column

innodbMySQL

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?

Akina suggested:

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?

Best Answer

Create a table (done 1 time)

CREATE TABLE salary 
( employee_id INT NOT NULL, 
  amount DECIMAL(10, 2) DEFAULT NULL, 
  ts DATETIME DEFAULT '1000-01-01 00:00:00'
              ON UPDATE CURRENT_TIMESTAMP );

Insert 12 records for new employee (done 1 time when creating a new employee)

INSERT INTO salary (employee_id)
SELECT @new_employee_id
FROM ( SELECT 1 UNION SELECT 2 UNION .. UNION SELECT 12 ) numbers;

Add new data (done monthly)

UPDATE salary
SET amount = @new_amount
WHERE employee_id = @employee_id
ORDER BY ts ASC LIMIT 1;
Related Question