Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
What DBMS is this for? In SQL Server you could do some fun stuff with hierarchyid (Transact-SQL) –  Mikael Eriksson Jul 12 '13 at 10:15
    
I use MySQL.... –  Arash Mousavi Jul 12 '13 at 10:17
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
add comment

1 Answer 1

up vote 6 down vote accepted

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 |
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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