-1

I'm swapping two adjacent column values from the same table. I feel like the way I'm doing it can be optimized without the need for a transaction, but I haven't managed to reduce it.

 START TRANSACTION;
 UPDATE comparisons 
    SET comparisons.position = (@pos := comparisons.position - 1) 
    WHERE comparisons.id = ? LIMIT 1;
 UPDATE comparisons 
    SET comparisons.position = (@pos + 1) 
    WHERE comparisons.position = @pos 
    AND comparisons.id <> ?;
 COMMIT;

Does anyone have experience with this type of problem and managed to come up with a more efficient solution?

blend
  • 132
  • 1
  • 9
  • 1
    What makes you think eliminating the transaction will optimize it? What benchmarks have you done to determine if it's slow? Assuming comparisons.id is indexed, that should be very quick. If you told us the purpose of the query we could help better, it looks like you're moving the position of an item in a list? – Schwern Mar 29 '15 at 18:42
  • Are there any particular problems with these 2 queries? – zerkms Mar 29 '15 at 18:45
  • @Schwern "Assuming comparisons.id is indexed, that should be very quick" --- the second query will not be, since it's a comparison for non-equality. – zerkms Mar 29 '15 at 18:46
  • @zerkms In that case, `comparisons.position` would have to be indexed. – Schwern Mar 29 '15 at 18:49
  • The transaction works and is fast enough for the current needs of the application. But I don't think this particular query is as webscale as it could be. Mainly the `AND comparisons.id <> ?` condition. It's more of a logic problem. I've played with the order of updating but I can't seem to eliminate the need for the check. Thus I think I need a different perspective. – blend Mar 29 '15 at 18:50
  • @blend you haven't explained the task. You haven't provided the table schema. – zerkms Mar 29 '15 at 18:51
  • The id is indexed and the position is used to order the rows for a select on all rows of a specific id. In my actual implementation the direction ie. "1" in this example can be either 1 or -1. – blend Mar 29 '15 at 18:53
  • Sorry, it's not a task explanation. Please bother to spend few minutes and provide the proper description. It's in your interest to do that in a good way. – zerkms Mar 29 '15 at 18:56
  • possible duplicate of [SQL UPDATE statement to switch two values in two rows](http://stackoverflow.com/questions/8108798/sql-update-statement-to-switch-two-values-in-two-rows) – Schwern Mar 29 '15 at 19:04

2 Answers2

2

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.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • But the WHERE is evaluated before the value is set, correct? The first update makes the two column values have the same value until the second update gets evaluated. So the row from the first update could potentially get updated in the second query? I'm not sure how I can make position unique. – blend Mar 29 '15 at 19:02
  • @blend Good catch. I found [an answer](https://stackoverflow.com/questions/8108798/sql-update-statement-to-switch-two-values-in-two-rows) which covers the case where you know the IDs you want to swap. I also put together something simple to cover moving a row around. – Schwern Mar 29 '15 at 19:23
  • @blend Ok, I give up on trying to make position unique. – Schwern Mar 29 '15 at 19:38
  • Haha, no worries. Thanks for helping anyway. I'm going to make sure I implement your other advice and just leave my algorithm how it is for now and change it if it becomes a problem later. – blend Mar 29 '15 at 19:44
0

Once you have @pos, this will do both UPDATEs in a single statement:

UPDATE tbl
    SET pos = @pos+@pos+1 - pos
    WHERE pos IN (@pos, @pos+1);
Rick James
  • 135,179
  • 13
  • 127
  • 222