What is the best approach to sort MySQL table rows by user choice?

Question

Suppose we have a MySQL table (id , name) and a drag-and-drop-enabled list view of the table. User drags 90th row and places it over 10th row. What is the best approach to keep this new sorting?

• I do not mean to keep sorting for every use separately
  
• The HTML/JavaScript is not the problem
  

I have seen some programmers add a weight column to table, setting lower number upper in the table. In my example it will be 1 to 100. The problem is in case of above example (90 to 10) updating 81 rows is required. 90 changes to 10 and each 10 to 89 increments. Is it efficient in MySQL? Is there any better solution?
Another way maybe is saving new order as a string in another table, but in this case we lose MySQL sorting in retrieval phase!

I remember when we learned trie tree structure in university as an indexing tool, we said wow! Even when we took advantage of every single bit of a byte, 1.5 GB of pure text data stored in less than 500KB!!! So right now I still search to find a better answer!

Answer 1

Even programming languages when updating indexes for an array that had an object added in a previously occupied index adds one to each index.

You're going to have to end up updating the index for every single row.

The cleanest way to do that however would be something like this (where people is a non trivial table of course):

UPDATE people
SET index = index + 1
WHERE index BETWEEN $newIndex AND $oldIndex;

depending on your database, between might include or exclude the high and low bound numbers. Just make sure you know how it treats them!