Re: Foreign Keys as to affect impact on performance

[Rick James]

Embellishing on Peter's comments...

When INSERTing, the FOREIGN KEY CONSTRANT involves reaching to the other table and doing a lookup in an index to validate the constraint. This lookup costs something. In huge tables it can cost a lot if it needs to hit the disk.

The INDEX mentioned above was either explicitly created by the DBA, or was implicitly created by the FK.

Secondarily, if you don't need that INDEX otherwise, the existence of another INDEX slows down INSERTing -- because all indexes are updated on all INSERTs. (You probably _do_ need the index -- for JOINs.)

When SELECTing, the FKs have zero impact. JOINs are likely to use whatever INDEXes are appropriate, which might include the INDEX created by the FK.

To put performance in perspective, If you have thousands of rows, or even millions of rows in the tables, and if you are inserting only a few rows per second, the performance hit discussed here is usually insignificant. A billion-row table or INSERTing 1000 rows/sec -- that's a different matter.

> When I asked why, I was told that in order to improve performance all the foreign keys were removed.

Perhaps the advice came from a billion-row system, and perhaps it does not apply to your case.