Most efficient ordering post database design

Question

I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, But in thousands rows it's worst.

Is there any better design for ordering posts, that is more efficient?

• Andriy M: Can we assume that the post_order column is maintained to be always sequential (particularly after such operations as inserts and deletes)?

Answer 1

You can change your post_order column to a float and update the value with a calculation using the post_order values from the posts you want to end up between.

Example:

PostID, PostOrder
1 1
2 2
3 3

If you want to move PostID = 3 to be sorted between 1 and 2 it would be

PostID, PostOrder
1 1
3 1.5
2 2

Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID of the post to be moved and PostID of the post you want to end up behind.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

create table Post
(
 PostID int primary key,
 PostOrder float unsigned not null unique,
 check (PostOrder > 0)
);
insert into Post(PostID, PostOrder) values (1, 1);
insert into Post(PostID, PostOrder) values (2, 2);
insert into Post(PostID, PostOrder) values (3, 3);
insert into Post(PostID, PostOrder) values (4, 4);
insert into Post(PostID, PostOrder) values (5, 5);
insert into Post(PostID, PostOrder) values (6, 6);
insert into Post(PostID, PostOrder) values (7, 7);
insert into Post(PostID, PostOrder) values (8, 8);
insert into Post(PostID, PostOrder) values (9, 9);
insert into Post(PostID, PostOrder) values (10, 10);
//
create procedure MovePost(MovePostID int, AfterPostID int)
begin
 declare AfterPostOrder float;
 declare NextPostOrder float; 
 set AfterPostOrder = (select PostOrder 
 from Post
 where PostID = AfterPostID);
 if AfterPostOrder is null then
 -- Move first
 set AfterPostOrder = 0;
 end if;
 set NextPostOrder = (select min(PostOrder) 
 from Post 
 where PostOrder > AfterPostOrder);
 if NextPostOrder is null then
 -- Move last
 set NextPostOrder = (select max(PostOrder) + 2
 from Post);
 end if;
 update Post
 set PostOrder = (AfterPostOrder + NextPostOrder) / 2
 where PostID = MovePostID;
end

Query 1:

call MovePost (7, 3); -- Move 7 after 3
call MovePost (8, 3); -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10); -- Move 2 after 10
select * 
from Post
order by PostOrder

Results :

| POSTID | POSTORDER |
----------------------
| 9 | 0.5 |
| 1 | 1 |
| 3 | 3 |
| 8 | 3.25 |
| 7 | 3.5 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 10 | 10 |
| 2 | 11 |

• Mikael Eriksson: Same idea on StackOverflow Maintaining order of elements in MySQL database tables OR inserting new rows in specific positions for MySQL