MySQL update a sorting index column to move items

Question

If I have the following table & data to allow us to use the sort_index for sorting:

CREATE TABLE `foo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bar_id` INT(11) DEFAULT NULL,
  `sort_index` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `foo` (`bar_id`, `sort_index`) VALUES
 (1,1),(1,2),(1,3),(1,4),
 (2,1),(2,2),(2,3),(2,4),(2,5);

I want to be able to do the following in the most efficient manner:

1. Move a foo entry to a given position (scoped by the bar_id)
2. Ensure that the sort_index is always 1 indexed and has no gaps
3. You should be able to move items to the beginning and end of the list and rule #2 should still be applied
4. It should be done entirely in queries and as few as possible (as the sets could be very large and looping over them doing individual UPDATEs is not ideal)

To clarify what I'm trying to do, lets assume the table was empty so we have the following data:

id | bar_id | sort_index
1  | 1      | 1
2  | 1      | 2
3  | 1      | 3
4  | 1      | 4    
5  | 2      | 1
6  | 2      | 2
7  | 2      | 3
8  | 2      | 4
9  | 2      | 5

Then if we were to do the following moves

• foo 1 to sort_index 3
• foo 7 to sort_index 1
• foo 5 to sort_index 5

We should get the following data:

id | bar_id | sort_index
1  | 1      | 3
2  | 1      | 1
3  | 1      | 2
4  | 1      | 4    
5  | 2      | 5
6  | 2      | 2
7  | 2      | 1
8  | 2      | 3
9  | 2      | 4

And SELECT * FROM foo ORDER BY bar_id, sort_index; gives us:

id | bar_id | sort_index
2  | 1      | 1
3  | 1      | 2
1  | 1      | 3
4  | 1      | 4  
7  | 2      | 1
6  | 2      | 2
8  | 2      | 3
9  | 2      | 4
5  | 2      | 5

Answer 1

You should be able to do this in a single query: something along the lines of UPDATE foo SET sort_index = sort_index + 1 WHERE bar_id == b AND sort_index < s1 AND sort_index >= s2, where b is the bar_id of the row to be moved, s1 is the current sort_index of that row, and s2 is the the sort_index you want to move it to. Then, you'd just change the sort_index of the row.

You'd probably want to do the two queries inside a transaction. Also, it might speed things up if you created an index on the sort_index using something like CREATE INDEX foo_index ON foo (sort_index).

(By the way, here I'm assuming that you don't want duplicate sort_index values within a given bar_id, and that the relative order of rows should never be changed except explicitly. If you don't need this, the solution is even simpler.)