3 ms·
A less obvious referential integrity performance requirement is ensuring your FKs are indexed. This is not done by default or required by DBs but will be a per
by plasma 6y ago
A less obvious referential integrity performance requirement is ensuring your FKs are indexed.
This is not done by default or required by DBs but will be a performance penalty if you don't do it.
For example, having a User (Id, Name) table, and an Order (UserId, Amount) table, you may have a FK between User.Id <-> Order.UserId.
If a User record is deleted, the database needs to ensure no Order exists with that UserId to maintain the FK integrity, so will do something like an (SELECT Count(*) FROM Order WHERE UserId = @DeletingUserId) to check if its OK to delete that User record.
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.
This works the other way too, if you insert an Order row, the inserted UserId is looked up on the User.Id table to assert it exists, so an index is needed there too (although, User.Id being the primary key, its already indexed), but you get the ideal.
- pizza234 6y ago> A less obvious referential integrity performance requirement is ensuring your FKs are indexed. For the sake of precision, this is a requirement in MySQL¹: > MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan ¹=https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign...
- trhway 6y ago>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.