I was recently tasked with making a table in a MySQL database sortable by the user. There are a few steps involved to take care of new records, while providing existing records with a default value.
The new records would be created with a
ifnull(max(order), 0) + 1 to assign them the next position. This was sufficient since I was positioning based on
created_at and a new record was guaranteed to have the highest
For the existing records, I wanted to:
- group by
- set the
order(int) column to an incrementing integer
- with an initial ordering based on a
The first approach was to select groups of users, and for each group sort the records then update each record. This approach would work, but it wouldn’t have good performance. I really wanted to do this in a single update statement.
I ended up going with an approach based on this article :
set @user_id = 0, @order = 1 update table1 as dest, ( select x.id, x.user_id, x.created_at, @order := if(@user_id = x.user_id, @order + 1, 1) as `order`, @user_id := x.user_id as dummy from ( select id, user_id, created_at from table1 order by user_id, created_at ) as x ) as src set dest.`order` = src.`order` where dest.id = src.id;
Let’s break this down:
- We are using database session variables to keep track of some state. We default to
@user_id = 0and
@order = 1
- Working from from the inner-most select, we select the
created_at(timestamp) as these are the columns needed to do our calculations for the new
ordervalue. We order these by
created_atto get the sort ready. This dataset is aliased as
x(yes I know I’m terrible at naming)
- The next select up pulls the same
created_atfrom the inner-most select and does some calculations persisting the state in the session variables we setup in step 1. The actual work here is:
- Calculating the order:
@order := if(@user_id = x.user_id, @order + 1, 1). The
:=is an assignment in SQL. We set the
@orderto either a default of 1, or the
@order + 1.
- Comparing user to determine what to set the
@orderto. This is done in a conditional expression:
if(@user = x.user_id). Because the
@user_idis set to the
x.user_idvalue on each row, the comparison should return true until we get to the next user, at which point the
@orderwill reset to 1.
- Calculating the order:
- Finally the outer
updatetakes the resulting
srcdataset and does some updating. We constrain to
where dest.id = src.idto get the correct record, and then we
set dest.order = src.orderwhich is our calculated value.
Performance is looking great. On a dataset of 300k records, this runs in 3 seconds on a 4 CPU Docker container.
Bonus: If you have additional criteria (like only sorting favorite records for example) this can be easily applied to the inner-most
SELECT in the where clause!