Search code examples
mysqlforeign-keysinnodb

MySQL does FK reduce insert/update operations?

Does anybody know, does FK reduce insert/update operations in MySQL? I use engine INNODB.

Solution
    • Having a FK on a table implicitly creates (and maintains) an index.
    • When doing certain write operations, the FK's implicit INDEX is checked to verify the existence of the appropriate row in the other table. This is a minor performance burden during writes.
    • When doing SELECT ... JOIN for which you failed to explicitly provide the appropriate index, the implicit index produced by some FK may come into play. This is a big benefit to some JOINs, but does not require an FK, since you could have added the INDEX manually.
    • If the FK definition includes ON DELETE or UPDATE, then even more work may be done, especially for CASCADE. The effect of CASCADE can be achieved with a SELECT plus more code -- but not as efficiently as letting CASCADE do the work.
    • FKs are limited in what they can do. Stackoverflow is littered with question like "How can I get an FK to do X?"

    Does any of this sound like "reducing insert/update operations"?