Re:GitHub's online schema migration for MySQL
by gaius on Thursday May 03, 2018 @06:19AM
(#16984655)
How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?
[ Parent
| on HN ]
Re:GitHub's online schema migration for MySQL
by tomnipotent on Thursday May 03, 2018 @12:25PM
(#16987363)
It repeatedly retrieves the parent row. Look at the URL when replying on HN and change the id by adding a few 0's - the app prevents a comment from being added if the ID isn't found. Of course, this doesn't prevent a developer from circumventing the apps constraint logic.
Re:GitHub's online schema migration for MySQL
by gaius on Thursday May 03, 2018 @12:50PM
(#16987583)
This would place a far higher load on the DB (which would validate the constraint via the index if allowed to) and create more network roundtrips. It’s 10x as expensive as just using a FK, and far less reliable!
Re:GitHub's online schema migration for MySQL
by tomnipotent on Thursday May 03, 2018 @08:26PM
(#16991211)
>> It’s 10x as expensive as just using a FK
It's not about load, it's about locks and contention in the database caused by FK constraint enforcement. Extra read queries will barely be noticeable compared to that.