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 |
Best Answer
100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.
Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new question for feedback.