EXPLAIN EXTENDED

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

• Sorting lists
• Sorting lists: moving items
• Sorting lists: adding items
• Sorting lists: deleting items
• Sorting lists: moving blocks
• Sorting lists: deleting blocks

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 »