How to do a (adjustable?) sort?

Question

MySQL

There are users 'users' (id, name)
and each user can create their own categories 'categories' (id, user_id, title)
It is necessary to make it so that the user can change the categories in places
For example:
1 - first category
2 - second category
3 - third category
And he can just swap 3k in place of 1k and it will look like this:
1 - third category
2 - second category
3 - first category
For now, I just added an 'index' field to 'categories'. Thus, before writing a new category, I simply count how many categories the user has and assign this number to index
When deleting a category, I do an index-- to all categories whose index was greater than that of the deleted category
, when reordering, I get oldIndex and newIndex from the moved category, and depending on what is more or less, I change the index of those categories that are in the range from old to new
But here a problem arises
if we have 1000000 categories and a person drags from 1000000 places to 1, then there will be 1000000 + 1 records in the database
because I did not find how to automatically change +1/-1 using mysql or laravel,
so I have to take in laravel collection of elements and foreach
Maybe there is a better solution?

Answer 1

UPDATE `categories` 
  SET `index` = `index`+1 
  WHERE `index` BETWEEN :firstIndex AND :lastIndex
    AND `user_id` = :userId