Re-ordering large amounts of data in MySQL without mass UPDATEs

[jggretton]

Hello, I currently have a mysql table structure similar to:

 

  id    |    name  |  order

------  -----------  ---------

  1            cat            4

  2            dog          3

  3            fish          1

  4          donkey        2

 

And when I output items I can use a query along the lines of:

 

"SELECT `name` FROM `table` ORDER BY `order` ASC"

 

which would return:

 

fish

donkey

dog

cat

 

New items get a `order` value equal to their `id` and so appear at the end of the list by default.

 

I've got a CMS which allows me to re-order the items by moving each up or down in the list. For instance, in the data above if I asked `dog` to move up, it would swap it's `order` attribute with donkey's. The table would then look like:

 

  id    |    name  |  order

------  -----------  ---------

  1            cat            4

  2            dog          2

  3            fish          1

  4          donkey        3

 

And my query would return:

 

fish

dog

donkey

cat

 

This is fine for this amount data, but what if I had 1 million entries and I wanted to move 'dog' from position 1'000 to the top of the list (without reordering the other data), this would take 1000 individual swaps of the `order`s in the database. What if I wanted to insert a new item in position 500'000? Using the technique above I would have to update the `order` attribute of half a million database entries!

 

Does anyone know of a better system for storing the order of items in a MySQL database? So far the only one I've found is using floating point numbers and setting the `order` =  ( order above + order below ) / 2 but this does not sound very robust to me!

 

Any thoughts / links would be greatly appreciated!

 

Many thanks, James

 

 

[rarebit]

This solution is quite app specific but if it was the other way around, e.g. the highest num was first, then you just shove on top, however at some point this would peter out, so you'd need to do the big reorder once in a while (e.g. cron).

 

 

Another idea is to use say chapters and then you move it to new chapter and if necessary reorder but that chapter...

[jggretton]

Thanks for your reply Rarebit, unfortunately having the order the other way round wouldn't help me insert an item in a particular position in the list.

 

I hear your comments about an occasional cron fix of the table though. This would make the floating point method more sustainable, but it still doesn't satisfy my quest for the nice clean solution!

 

Any other thoughts anyone?

 

Many thanks, James

[PFMaBiSmAd]

The type of data that would need to be dynamically reordered, such as a menu in a CMS, would never have more than a few 10's of values. The way to set up the values would be to leave gaps in the "order" values. Use 100, 200, 300, 400 ... instead 1,2,3,4... The size of the gaps you choose depends on how many values you expect in the list, how often it will be reordered, and how often you want to "clean up" the list by going through (cron job) and reassign all new values to restore the gaps.

 

You would not use an "order" column for general data. You would order that by a date column or some other unique piece of information or you would use a "favorites" table to list a set of specific information from the general data and store the "order" in the "favorites" table.

 

If you give a specific example of what the data is, someone can give a specific example of how to deal with arbitrarily ordering it.

[jggretton]

Hi PFMaBiSmAd, thanks for the reply and sorry for my very slow response!

 

The reason this problem has come up is because I have been focussing my efforts recently on creating some general reusable php classes for use in all my php projects.

 

While making my SortData class (which theoretically could be used for any db with an order column) I was planning some of the public functions - moveUp(id), moveDown(id), swap(id1,id2) where easy, but positionAfter(id1,id2) posed the problem discussed.

 

I completely hear what you say about usually only having a handful of data to re-sort when allowing manual resorting, but the problem still intrigued me and so even without a specific need I am still interested in finding a solution – or showing that there is no truly graceful solution!

 

The cron / triggered when required solution certainly falls under the ungraceful category at the moment…

 

 

[moselkady]

 

If you want to re-order 'cat' from 4 to become 2, maybe you can do something like this:

 

UPDATE mytable SET `order`=-1 WHERE `order`=4
UPDATE mytable SET `order`=`order`+1 WHERE `order` BETWEEN 2 AND 3
UPDATE mytable SET `order`=2 WHERE `order`=-1

 

I am not sure if this is what you are looking for though

[jggretton]

Interesting, I didn't know that this was possible with MySQL... I wonder how much processing this would take on a mySQL server? ie. if we had:

 

UPDATE mytable SET `order`=`order`+1 WHERE `order` BETWEEN 2 AND 9999999

 

I might do a little performance testing and see!

 

Thanks for your suggestion!