0

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?

5
  • IODKU and REPLACE both rely on an a primary key or unique key. I can't see a logical formation of such in your structure. For 12 rows I think your solution is ok, just wrap it in a transaction.
    – danblack
    Apr 2, 2020 at 8:36
  • @danblack Yeap, that's what I understood from the documentation and as I couldn't find out how it can be better than my approach, I thought I'm missing something. Thanks by the way.
    – rez
    Apr 2, 2020 at 8:38
  • That seems like it could be really inefficient? One-query solution which guarantees that the amount of records is always 12, is less efficient than the solution with 2 queries and without records amount "not above 12" guarantee? Really?
    – Akina
    Apr 2, 2020 at 10:01
  • @Akina One query? Can you demonstrate how can I do it using one query? If you are able to do it using one query, that's the title of my question actually and you can answer it.
    – rez
    Apr 2, 2020 at 15:02
  • @Akina Sorry, I get it now, I think it is as efficient as my current approach, because your method also needs 2 queries right? One to select the count of rows (Probably fetch the most out dated "timestamp" and probably add a primary key to the table), one to either insert a new row or update the oldest row.
    – rez
    Apr 2, 2020 at 15:09

1 Answer 1

1

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;
2
  • But it's actually a table for my users and employee was just an example, I don't want unnecessary records for all the users who haven't that triggered the log. Imagine thousands of users registering and those logs created without they doing anything.
    – rez
    Apr 2, 2020 at 15:46
  • You know? I didn't know "OFFSET" doesn't work on DELETE statements and nobody even mentioned it to me. Your method is the best method mixed with a select query. Thank you.
    – rez
    Apr 2, 2020 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.