What is the most efficient way to store a sort-order on a group of records in a database?

Assume PHP/MYSQL but I don't necessarily need actual code, I'm just interested in the theory behind it.

A good use-case would be Facebook's photo gallery page. You can drag and drop a photo on the page, which fires an Ajax event to save the new sort order. I'm implementing something very similar.

For example, I have a database table "photos" with about a million records:

photos id : int, userid : int, albumid : int, sortorder : int, filename : varchar, title : varchar

Let's say I have an album with 100 photos. I drag/drop a photo into a new location and the Ajax event fires off to save on the server.

Should I be passing the entire array of photo ids back to the server and updating every record? Assume input validation by "WHERE userid=loggedin_id", so malicious users can only mess with the sort order of their own photos

Should I be passing the photo id, its previous sortorder index and its new sortorder index, retrieve all records between these 2 indices, sort them, then update their orders?

What happens if there are thousands of photos in a single gallery and the sort order is changed?

-------------Problems Reply------------

What about just using an integer column which defines the order? By default you assign numbers * 1000, like 1000, 2000, 3000.... and if you move 3000 between 1000 and 2000 you change it to 1500. So in most cases you don't need to update the other numbers at all. I use this approach and it works well. You could also use double but then you don't have control about the precision and rounding errors, so rather don't use it.

So the algorithm would look like: say you move B to position after A. First perform select to see the order of the record next to A. If it is at least +2 higher than the order of A then you just set order of B to fit in between. But if it's just +1 higher (there is no space after A), you select the bordering records of B to see how much space is on this side, divide by 2 and then add this value to the order of all the records between A and B. That's it!

(Note that you should use transaction/locking for any algorithm which contains more than a single query, so this applies to this case too. The easiest way is to use InnoDB transaction.)

Store as a linked list, sortorder is a foreign key reference to the next photo_id in the set.

this would probably be a 'linked list' construct.

To me the second method of updating is the way to go (update only the range that changes). You are mentioning "What happens if there are thousands of photos in a single gallery ...", and to me that is never going to happen. Lets take your facebook example. Facebook doesn't show thousands of photos on one page, they split it up to about 10-20 per page.

The way I'd do this in a nonrelational database is to store a list of photo IDs on the 'album' entity/record, in the order desired. Reordering the photos results in reordering the list, and only a single database write.

Some SQL databases (Eg, PostgreSQL) have native list datatypes, but MySQL doesn't. You could serialize the list as a string or binary on MySQL.

3rd-normal-form trained database gurus will scream at you that this is a terrible approach, but RDBMSes are optimized for OLAP type queries, where query flexibility is more important than read performance. Webapps are best written with a 'write heavy, read light' strategy in mind, and this sort of denormalization is exactly in line with that.

Category:mysql Views:0 Time:2011-07-24

Related post

  • Looking for an efficient way to find a sorted order from 2 lists 2009-09-24

    I have 2 set of unsorted integers: set A and set B. But we don't know how many items are there in setB in advance. I need to : while setA and setB are not empty: pop the smallest no from setA move an int from setB to setA What is the most efficient w

  • What's the best way to store sort order in SQL? 2010-05-13

    The guys at the top want sort order to be customizable in our app. So I have a table that effectively defines the data type. What is the best way to store our sort order. If I just created a new column called 'Order' or something, every time I update

  • What is the most efficient way to store and query trees? 2009-05-07

    I need to analyze 1 TB+ of web access logs, and in particular I need to analyze statistics relating to requested URLs and subsets of the URLs (child branches). If possible, I want the queries to be fast over small subsets of the data (e.g. 10 million

  • What is the most efficient way to store measurements with a variable number of fields in a database? 2009-10-27

    We have a data collection system that collects measurements from environmental sensors that measure velocity of water flowing through a river or channel. Each measurement generates a fixed number of values (e.g. Date, Time, Temperature, Pressure etc.

  • what is the efficient way to store session variable 2009-11-19

    My asp.net application is deployed on a load balance system. And I just want to keep a user's role (a string) in a whole session. I was told that session info in asp.net is stored in the database so I don't want to the asp.net engine to access DB eca

  • Java: Most efficient way to store/retrieve workout information from a file? 2009-11-20

    I'm working on a Java project for class that stores workout information in a flat file. Each file will have the information for one exercise (BenchPress.data) that holds the time (milliseconds since epoch), weight and repetitions. Example: 1258355921

  • Efficient method to store Python dictionary on disk? 2010-01-14

    What is the most efficient method to store a Python dictionary on the disk? The only methods I know of right now are plain-text and the pickle module. Edit: Sorry for not being very clear. By efficient I meant fastest execution speed. The dictionary

  • Most efficient way to store IP Address in MySQL 2010-03-29

    What is the most efficient way to store and retrieve IP addresses in MySQL? Right now I'm doing: SELECT * FROM logins WHERE ip = '' Where ip is a VARCHAR(15) field. Is there a better way to do this? --------------Solutions------------- For IPv

  • Efficient way to store a graph for calculation in Hadoop 2010-05-10

    I am currently trying to perform calculations like clustering coefficient on huge graphs with the help of Hadoop. Therefore I need an efficient way to store the graph in a way that I can easily access nodes, their neighbors and the neighbors' neighbo

  • Whats the most efficient way to store an array of integers in a MySQL column? 2010-07-16

    I've got two tables A: plant_ID | name. 1 | tree 2 | shrubbery 20 | notashrubbery B: area_ID | name | plants 1 | forrest | *needhelphere* now I want the area to store any number of plants, in a specific order and some plants might show up a number of

  • What's a succinct, useful and efficient way to store large time-series in F#? 2010-12-08

    I'm currently learning F# and I'm exploring using it to analyse financial time-series. Can anyone recommend a good data structure to store time-series data in? F# offers a rich selection of native types and I'm looking for a some simple combination t

  • What is the most efficient way to store and access images 2011-01-08

    I am working on a project which has to store tens and thousands of images on a server and let the users access them. I need the most efficient method to store these images and to retrieve them. Also, I need information about which technology I should

  • What is the most efficient way to store a non-directed graph? 2011-02-14

    As the title says.. What is the most efficient way to store a connected graph in java? For example lets say I have multiple locations connecting to each other in various ways and I have to traverse through the graph to see if it's connected.. any hel

  • Most space efficient way to store millions of simple data? 2011-07-09

    My data looks like this: 00000000001 : `12341234...12341234' Basically a unique id value associated with a big string of numbers (less than 100 chars). I want to store 10's of millions and maybe even 100's of millions of these pieces of data, just ID

  • Most efficient way to store queries and counts of large SQL data 2011-08-08

    I have a SQL Server database with a large amount of data (65 million rows mostly of text, 8Gb total). The data gets changed only once per week. I have an ASP.NET web application that will run several SQL queries on this data that will count the numbe

  • Most efficient way to store and access a huge data matrix in MySQL 2011-09-19

    I am going to store a huge amount of matrix data in a mysqlDB what is the most efficient way to store and access the data? The efficiency is most important when getting the data, the table will not be updated regularly. The matrix is about 100.000 ti

  • Most efficient way to store thousand telephone numbers 2011-10-07

    This is a google interview question: There are around thousand phone numbers to be stored each having 10 digits. You can assume first 5 digits of each to be same across thousand numbers. You have to perform the following operations: a. Search if a gi

  • What is the most efficient way to store 500.000 images? 2012-02-17

    I'm coding a basic gallery for a website with around 40.000 online people at any given time. Users will be able to create galleries and upload images. My question is, should I make a seperate folder for each gallery and put the images in them, or mak

  • Efficient Way to store a table of checkbox data into MySQL database 2012-03-08

    I have a table of data which consist of checkboxes and I want to find an efficient way to store it into MySQL database. For now, the HTML is like below <tr class="weekpattern"> <th class="collabel tlc"></th> <th class="collabel"

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.086 (s). 11 q(s)