How to create fast database queries

Archive for March 26th, 2009

Sorting lists: moving items

Comments enabled. I *really* need your comment

This is article 2 of 6 on linked lists in MySQL:

Today, I'll expain how to move items in these lists.

To move an item in the linked list we need to relink it. If we move item A after item B, we need to update at most three rows:

  1. A's parent is updated to B
  2. A's child's parent is updated to A's parent
  3. B's child parent is updated to A

B here may be a real row or a surrogate id of 0 which we use to designate the first row's parent.

Moving A after 0 means moving A to the top of the list.

Unfortunately we cannot rely on a single statement to perform these updates, because we have a UNIQUE INDEX on parent.
Read the rest of this entry »

Written by Quassnoi

March 26th, 2009 at 11:00 pm

Posted in MySQL