Maintaining order of large sorted list

[bjmosk]

Hi,

I have a table that will allow a user to sort a potentially large list of photos. Each photo has its own record in the table and there is a web interface to allow the user to specify the sort order of each one - this value is stored in its own "sort_order" column, separate from the primary key.

A user can have thousands of photos and I need to make sure the sequence is maintained properly - sequential integers with no gaps. It's easy to add a record, but I'm curious about the best way to insert one.

If a user has 3,000 photos and they add a new one into position #2, that would mean to maintain the order I would need to increment the "sort_order" column of positions 3 thru 3,000 by 1.

I feel like running almost three thousand update queries might not be the best solution to such a seemingly simple problem... or is it?

Any advice would be greatly appreciated.

Thanks, Brian

[coopster]

Welcome to WebmasterWorld, bjmosk.

Don't run 3,000 update queries, run one UPDATE query and modify 3,000 rows, or in your example, 2,998 rows ;)

UPDATE myTable SET sort_order = sort_order + 1 
WHERE imagePrimaryKey = $someNumber AND sort_order >= 3;

If the sort_order column was part of a compound key or if it has a UNIQUE constraint you may have to include an ORDER BY clause. From your explanation, this does not seem to be the case though.

[bjmosk]

Thanks. Just curious - regarding performance and query reliability, how hard should I try to avoid running queries that update thousands of rows? Should I not worry about running queries like this, or should I be using it as a last resort?

-Brian