Re:Feature Casualties of Large Databases
by trhway on Tuesday December 01, 2020 @10:57PM
(#25273092)
>If you do not have an index on Order.UserId column, then the deletion of the User record will be slow when the Order table is large.
not only slow per.se. The full table scan in this case would also usually take the lock on the whole Order table instead of only narrow block/range-level lock in the index like it would in the case of indexed FK. Such table lock may force some other threads/sessions to wait, and provides increased surface for deadlocks between seemingly unrelated transactions.
not only slow per.se. The full table scan in this case would also usually take the lock on the whole Order table instead of only narrow block/range-level lock in the index like it would in the case of indexed FK. Such table lock may force some other threads/sessions to wait, and provides increased surface for deadlocks between seemingly unrelated transactions.