I need to create a schema for a queue. Elements in this queue will be processed in an order based on a column. However, this is not always the e.g.
Users should be able to freely re-order items in the queue, thus setting a custom position - let's call that field
position. Therefore, queue items that have a position
>= to the new position I set for a specific item need to be re-ordered on each update.
I really want to avoid using a server-side script to manually go through each of the now outdated elements and updating their position.
Is there a way to do this automatically, and with relatively low resource usage? I'm sure people have implemented such a queue in MySQL before, but there don't seem to be relevant answers anywhere, although the task sounds trivial. Any ideas?
I know I shouldn't rely on the order the records are stored in. But I am maybe considering using
ALTER TABLE ORDER BY. How bad is this really? And how useful is it in achieving my goal?
There's some PK, likely an auto-increment, or maybe the
date_added with more precision. The version is InnoDB, MySQL >= 5.6.
The queue position can be changed an unlimited number of times. It should work without failure. The UI with HTML and JS is trivial: On any change of immediate hierarchy within a container, you just read the order of elements with a specific attribute. It's also pretty common. Possibly hundreds of items in the list.
By "outdated elements" I mean those that no longer have the
position field set correctly, and need to be "reindexed". Naturally, something in MySQL would deal with that.
As for finished, I need those to be retrievable too, so they stay in the table, and are marked as done with a boolean field. There is another table that takes care of more complicated status transition, as it is not a part of what makes a queue.