Menu
As I understood from this post, there are some scenarios where foreign keys can improve query performance.
I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true?
1) The term query seems to be misleading. I am interested in all kinds of performance penalties.
2) Does anyone have any real-world numbers about the negative impact on INSERT, DELETE or UPDATE statements (I know it depends on the specific system, but nevertheless any kind of real-world measurements would be appreciated)?
864
It's a common mistake to avoid creating foreign keys in a database because they negatively impact the performance. It is true that foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking, but they improve the overall performance of a database.
Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.
Foreign keys will not adversley affect query performance in most cases, and are strongly recommended. By aiding normalization, you will eliminate redundant data, and if you follow up by adding underlying indexes (for the appropriate foreign key) you will get good performance on your queries.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
if a foreign key is needed for referential integrity then the presence of the foreign key should form the baseline for performance
you might as well ask if a car can go faster if you don't put seats in - a well formed car includes seats as much as a well formed database includes foreign keys
116
I'm assuming that for INSERT queries, constraints - including foreign key constraints - will slow performance somewhat. The database has to check that whatever you've told it to insert is something that your constraints allow it to insert.
For SELECT queries, foreign key constraints shouldn't make any changes to performance.
Since INSERTS are almost always very quick, the small amount of extra time won't be noticeable, except in fringe cases. (Building a several gigabyte database, you might want to disable constraints and then re-enable later, as long as you're sure the data is good.)
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
