mysql - How do I store orders?

Question

I have an app which has tasks in it and you can reorder them. Now I was woundering how to best store them. Should I have a colomn for the ordernumber and recalculate all of them everytime I change one? Please tell me a version which doesn't require me to update all order numbers since that is very time consuming (from the executions point of view).

This is especially bad if I have to put one that is at the very top of the order and then drag it down to the bottom.

• Name (ordernumber)

--

• 1Example (1)
• 2Example (2)
• 3Example (3)
• 4Example (4)
• 5Example (5)

--

• 2Example (1) *
• 3Example (2) *
• 4Example (3) *
• 5Example (4) *
• 1Example (5) *

*have to be changed in the database

also some tasks may get deleted due to them being done

Answer 1

You may keep orders as literals, and use lexical sort:

1. A
2. Z

Add a task:

1. A
3. L
2. Z

Add more:

1. A
4. B
3. L
2. Z

Move 2 between 1 and 4:

1. A
2. AL
4. B
3. L

etc.

You update only one record at a time: just take an average letter between the first ones that differ: if you put between A and C, you take B, if you put between ALGJ and ALILFG, you take ALH.

Letter next to existing counts as existing concatenated with the one next to Z. I. e. if you need put between ABHDFG and ACSDF, you count it as between ABH and AB(Z+), and write AB(letter 35/2), that is ABP.

If you run out of string length, you may always perform a full reorder.

Update:

You can also keep your data as a linked list.

See the article in my blog on how to do it in MySQL:

• Sorting Lists

In a nutshell:

/* This just returns all records in no particular order */

SELECT  *
FROM    t_list

id      parent
------- --------
1       0
2       3
3       4
4       1

/* This returns all records in intended order */

SELECT  @r AS _current,
        @r := (
        SELECT  id
        FROM    t_list
        WHERE   parent = _current
        )
FROM    (
        SELECT  @r := 0
        ) vars,
        t_list

_current id
-------  --------
0        1
1        4
4        3
3        2

When moving the items, you'll need to update at most 4 rows.

This seems to be the most efficient way to keep an ordered list that is updated frequently.