1

When using MySQL (actually, Aurora), will inserting into a table with a foreign key constraint be slower than the same table without the constraint?

  • Why don't you test it and see? Also, use EXPLAIN to determine this if you only have a few test data points - that way you'll see what is "touched" by the INSERT. Also, if you are doing this to "improve" performance because you've been told by some deluded person that FOREIGN KEYs are a performance killer and it's better to do checks in your app, have this person committed to a suitable institution immediately! – Vérace Mar 22 '16 at 14:55
  • 1
    Is driving a car without brakes faster than driving one with brakes? – Neil McGuigan Mar 22 '16 at 19:03
  • The data we are inserting is write-only: it will be pulled into a reporting tool nightly, and never used by the application. So yes, a car without brakes is faster than a car with them since you reduce the weight. This is great as long as stopping is not a requirement. – chris Mar 22 '16 at 19:54
  • You can have never updated and never deleted data (i.e.: insert and select only). If you ever have "write-only" data, just don't even write it. – joanolo Apr 20 '17 at 6:40
2

INSERT (etc) must check that there are no FOREIGN KEY violations. Hence it is slower. Think of it as checking to see if an entry is in an index.

  • How much it slows things down, and whether the cost/profit makes sense... is a different story, however. – joanolo Apr 20 '17 at 6:41
  • It's a small percent. The actual cost depend heavily on whether the block of the other index is currently cached, which depends on size of entire dataset, workingset size, speed of disk, etc. I have not encountered a case where FKs made the difference between the system being 'viable' versus 'too slow'. – Rick James Apr 20 '17 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.