How to auto assign ordinal ranking in mysql with as minimal queries for new rows?
If you are content with adding and deleting a view_id per user_id, here is what I see

Adding View 12 To User 10

I see three queries


Insert new view into user with BogusRank
Move all rank down below NewRank
Change BogusRank to New Rank for new view


Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking &gt;= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;


Deleting View 12 From User 10

I see three queries


Retrieve Row With View to Delete
Move All View Up in Rank
Delete Row with Old View


Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SELECT id,ordinal_ranking INTO @id_to_zap,@rank_to_zap FROM userviewranks
WHERE view_id = @view_id AND user_id = @user_id;
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking - 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking &gt; @rank_to_zap;
DELETE FROM userviewranks WHERE id = @id_to_zap;