4

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?

  • 1
    Can we assume that the post_order column is maintained to be always sequential (particularly after such operations as inserts and deletes)? – Andriy M Jul 12 '13 at 20:10
6

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 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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