I have a table that looks something like:
name: posts
columns:
- id
- sequence_id
- text
- like_count
The ID is a standard auto-incremented unique integer index.
The sequence ID should be similar — it is also a unique integer index.
The difference is that I want to increment it to the new maximum value in the table on update or insert not just insert.
Currently I accomplish this with a Redis counter that I increment before inserting into the database.
I’d like to drop the Redis dependency, though, and do this with purely MySQL if possible.
One option I thought of was creating a post_updates table which just has an auto-incrementing ID which I use the same way but this feels worse.
Another option is doing a full column scan to do max(sequence_id) + 1 but that isn’t really scalable and it would have race conditions.
Are there some better options I’m not aware of?
There's a solution in the manual to simulate a sequence object in MySQL:
CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);
The sequence table doesn't need an auto-increment itself, and it stores only one row.
When you are ready to increment your sequence_id in the table you describe, you first update the sequence value in the following manner:
UPDATE sequence SET id = LAST_INSERT_ID(id+1);
Now you can use that value when inserting/updating your table:
INSERT INTO posts SET sequence_id = LAST_INSERT_ID(), text = '...';
or
UPDATE posts SET sequence_id = LAST_INSERT_ID(), like_count = like_count+1;
Note that this is a little bit more heavy than an auto-increment, because updating the sequence table creates a row lock, not just an auto-increment lock. This could put an upper limit on the rate of traffic, because many concurrent clients trying to access this table would queue up on each other.
If you want a very high-throughput solution, I'd recommend to continue using Redis.