Why does the result of the selection change when putting down the MySQL index?

Question

MySQL

There is a table with columns id, sort and sort can go out of order. There is a slightly confusing query for moving a line to any position and shifting the sort values ​​​​of the previous / next element:

SET @start=37;
UPDATE tablename m INNER JOIN (SELECT @start AS oldsort, (@start:=testsort.sort) AS newsort from tablename AS testsort WHERE testsort.sort BETWEEN @start AND 39 ORDER BY testsort.sort DESC ) p ON m.sort = p.oldsort SET m.sort = newsort WHERE sort<=39 AND sort>37;
UPDATE tablename SET sort=39 WHERE id={ид_перемещаемого элемента}

In this example, we move the 37th element to the 39th position, respectively, for sort=38, the new sort becomes equal to 37, and for sort=39, the new sort becomes equal to 38. The
query works quite correctly, but after putting an index on the sort field, it breaks, and does not affect a single line! With what it can be connected? I would really appreciate any advice on how to resolve this issue...

Answer 1

I don't know why it works without an index, but I know why it doesn't work for a field with an index
When comparing BETWEEN, the condition BETWEEN 37 AND 39 falls on the first row, and BETWEEN 39 AND 39 on the second. And, accordingly, nothing falls under this condition.
That is, for everything to work, you need BETWEEN 37 AND 39 or BETWEEN 37 AND @start