There's not enough information to be sure, but if that query is slow (and I'm not saying it is) the first culprit would be that comparisons.position
is not indexed. That would make WHERE comparisons.position = @pos
in the second UPDATE quite slow for a large table. Check with EXPLAIN UPDATE ...
.
comparisons.id <> ?
is not going to be a problem because the results are already constrained by comparisons.position = @pos
.
Otherwise, transactions are not generally a source of performance problems. I'm sure someone can come up with a convoluted way to do that in a single, atomic statement, but it probably won't be any faster.
As for optimizing your algorithm, it looks like you're swapping element positions in the list. A few things will make this more performant.
- Make sure
comparisons.position
is indexed.
Make comparisons.position
unique.
- Allow general purpose position swapping, not just +1 and -1.
- Write a stored procedure.
Making position
unique guarantees there will be no duplicates... but it really complicates the algorithm. I'll work on it.
There's already an answer for swapping two rows' values, but MySQL doesn't let you do a subselect on the same table as you're updating so it doesn't work.
Allowing general purpose position swapping means if you want to move an element more than one position, you can do it in one set of queries and not N queries.
Making it a stored procedure has all the benefits of encapsulation, everything working with the data is using the same function. That function can have its guts changed later. Write one that takes an id and the number of positions to move it, and another one that takes two ids to swap.