0

I am currently designing a MySQL database that has 212 tables. Some of the tables has 5-7 foreign keys. Is this going to hamper database query performance in the long run? How many foreign keys are considered as standard practice and how much foreign key constraints are considered as too many?

2
  • A partially opinion based question. Consider that FK checks affect insert/modify and perhaps delete operations on those tables. If its almost always an read only (select) table, it doesn't matter what FK are there. If its heavily written and there's a low write latency requirements start looking carefully and benchmark the changes.
    – danblack
    Aug 4 at 7:05
  • Turn on the slowlog to help find which, if any, queries are bogging down the system. SlowLog
    – Rick James
    Aug 4 at 19:13

1 Answer 1

1

Foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking.

The main benefit of foreign keys is that they enforce data consistency, meaning that they keep the database clean.

There is no standard practice on how much foreign key constraints are considered to many.

Is this going to hamper database query performance in the long run?

If there are large INSERT/DELETE/UPDATE statements and the tables grows bigger you will be facing with performance issues even with correct indexes used.

A practical example on foreign keys you will find on

Hidden Cost of Foreign Key Constraints in MySQL

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.