Read my post again. If you delete row3, value of "position" for row4 will be decremented by 1, so, after delete it will have value of 3. This leads us to place where everything is alright.
Actually, your algorithm is broken. Suppose you add: row1, row2, row3, row4. The "position" value will be 4 (randint(1, 4)) Then you delete row3. The "position" value will be decrement to 3 (randint(1, 3)). How then will it ever randomly pick row4?
So if I understand, you're gaining some optimization for the random SELECT but you're causing an UPDATE on a possibly large number of rows for every DELETE? Example: if you DELETE the first row in the table, you now have to UPDATE every other row. Seems like this would be a high cost to pay, especially if your app does frequent deletes.
You are correct. There never is golden solution for each case. All tables have theirs specifics and where on solutions is great it is a failure in another one.
Rather than decrementing the position of every row above the deleted one, you should just give the highest one the position of the deleted one. So if you have rows 1, 2, 3, 4 and you delete 2, you have 1, 3, 2. That way you only have to make one update.
I dont think that would work, ie. we got 1,2,3,4. We delete 2 1,3,4 are left and we are missing 2. Flowing ur advice i can update last value (4) and get 1,3,2.
Now, I delete 1. 3,2 are left and updating last value wont give us a correct sequence
Comment
Read my post again. If you delete row3, value of "position" for row4 will be decremented by 1, so, after delete it will have value of 3. This leads us to place where everything is alright.
Parent comment
Actually, your algorithm is broken. Suppose you add: row1, row2, row3, row4. The "position" value will be 4 (randint(1, 4)) Then you delete row3. The "position" value will be decrement to 3 (randint(1, 3)). How then will it ever randomly pick row4?
Replies
I see! So the position is kept with the table.
But now you need an index on the position field too otherwise you can't pick from it quickly. Seems tight but extremely convoluted.
"On my desktop time to fetch 100random rows from 100000row table is.... ~40ms"
This was done without index and worked perfectly fine. Also, I'am glad that I finally make myself clear about this solution :)
So if I understand, you're gaining some optimization for the random SELECT but you're causing an UPDATE on a possibly large number of rows for every DELETE? Example: if you DELETE the first row in the table, you now have to UPDATE every other row. Seems like this would be a high cost to pay, especially if your app does frequent deletes.
You are correct. There never is golden solution for each case. All tables have theirs specifics and where on solutions is great it is a failure in another one.
Rather than decrementing the position of every row above the deleted one, you should just give the highest one the position of the deleted one. So if you have rows 1, 2, 3, 4 and you delete 2, you have 1, 3, 2. That way you only have to make one update.
I dont think that would work, ie. we got 1,2,3,4. We delete 2
1,3,4 are left and we are missing 2. Flowing ur advice i can update last value (4) and get 1,3,2.
Now, I delete 1. 3,2 are left and updating last value wont give us a correct sequence
No, you would replace the 3 with 1. That is the highest position value in the sequence 1,3,2. Not the *last* value in position, the *highest*.