2 ms·
To me it seems that spreading it over four tables would lead to a lot more potential read locks while the big combined table is waiting for a join on each of th
by noduerme 5y ago
To me it seems that spreading it over four tables would lead to a lot more potential read locks while the big combined table is waiting for a join on each of the others, and some process is trying to insert on the others and link them to the main. This is assuming they were using foreign keys and the main table 4-column index was unique.
- ericbarrett 5y agoOld best practice for InnoDB performance was actually to never use foreign keys because of these locking issues. Not sure if that's the case in 2021.
- noduerme 5y agoInnoDB uses row-level locking, and foreign keys are (usually) a great feature to ensure data integrity. But using multiple foreign keys from tables `a`,`b` as a composite index for table `x` can cause deadlock if both are being updated in rapid succession, because an update on `a` gets a lock on `x`, which needs a read lock on `b` which is waiting for the lock from `a` to be released. I try to never structure multiple foreign keys as a composite index.
- bt1a 5y agoThis is fascinating, thanks for sharing.