1

I need to store data related to timer sessions. My MySQL table looks like this:

user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
timer_start_time TIMESTAMP
timer_end_time TIMESTAMP

There are many users (>10 000) that do several (<10) timer sessions every day. However, I need to store only the 5 most recent ones. So there can only be 5 records per user. How do I design a schema that adheres to these requirements and remains performant?

I'm considering storing JSON objects in VARCHAR, but have heard that it's a bad practice.

I would prefer to use MySQL for this, but I would consider switching to any other database given the performance gains.

2
  • "most recent" -- based on start_time? or end_time?
    – Rick James
    Sep 24, 2023 at 14:42
  • Most recent records based on 'end_time'.
    – Kaspis245
    Sep 24, 2023 at 17:21

2 Answers 2

2

Plan A: Don't enforce the "limit of 5" when inserting. We can probably handle a billion rows without stressing performance or disk space.

Plan B: As you insert a new timer for a user, delete the oldest of 6 (if there are 6) for that user. That limits the table size to 50K rows -- a trivial table size.

I assume the "10" you mention is not enforced? If it is, perhaps the answer is to store 10 instead of 5. That allows for enforcing the two limits (in different ways).

This might suffice for removing the 6th oldest "timer" for a given user

DELETE FROM tbl
    WHERE user_id = ?
    ORDER BY end_time
    LIMIT 1 OFFSET 5;

Can a user have two timers with the same end_time? If not, then I recommend

PRIMARY KEY(user_id, end_time)

That will make some of the operations efficient.

You might also need

INDEX(end_time)

to handle other actions that you hinted at.

8
  • For plan A: I'm not fond of this strategy as I won't be using any of the records apart from the 5 most recently saved. Seems like a waste of space to store unusable data. For plan B: This seems reasonable in terms of scaling storage, but very expensive performance-wise. Would it really be that much worse to store JSON instead? Or maybe document databases would be better suited here?
    – Kaspis245
    Sep 24, 2023 at 17:22
  • 2
    JSON seems like the least efficient, due to difficulty in ordering/searching/updating. Sketch out the SQL for each case, then we can discuss space and performance in more detail.
    – Rick James
    Sep 24, 2023 at 18:05
  • @Kaspis245 "For plan B: This seems reasonable in terms of scaling storage, but very expensive performance-wise." - This is not the least bit expensive when indexed properly, especially on a tiny table like Rick points out yours would be. "Would it really be that much worse to store JSON instead?" - Agreed with Rick, that storing everything in a denormalized JSON structure would be inefficient.
    – J.D.
    Sep 25, 2023 at 2:00
  • @Kaspis245 Think of it this way, what's faster: Storing a single piece of paper per user with all 5 sessions written on it, in a random pile on the floor. Every time you need to add a session you need to find that piece of paper, shred it, and write a new one up - re-writing the other 4 sessions that didn't change (JSON approach). Or you store a separate piece of paper for each session per user, in a filing cabinet, sorted by user and session. Anytime you need to add a new session, you just find the oldest one in the sorted filing cabinet, shred it, and write only 1 session on a new paper.
    – J.D.
    Sep 25, 2023 at 2:06
  • @J.D. I guess it all boils down to the question of what is faster: finding 5 records, deleting the oldest of them, and inserting a new one. Or finding one record and updating its JSON value. I imagine that when the number of records is low, the first option is faster, but at some point JSON-based option overcomes in performance, no?
    – Kaspis245
    Sep 26, 2023 at 8:56
0

If you are absolutely sure about the 5x2 timestamp limit then you can set up a wide table with user_id and 5x2 timestamp columns, user_id becomes unique in the table and you update entire rows application-side. This would also allow you to move to NoSQL approaches indexed by user.

Migrating this data should be pretty trivial however, and you could generate the above schema with a short SQL view (to be materialised during downtime) so I would recommend sticking with the general-purpose table and migrating when you have an actual performance issue. At this time you will have a better understanding of performance-critical use cases/pain points so your testing and optimisation will be better. And who knows, you might find the historical logs useful in unanticipated ways.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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