CODEWITHSUNDEEP
mysqldatabasedatabase-designforeign-keysquery-optimization
Chaitanya Chikkala
Chaitanya Chikkala

Reputation: 143

What if I model a database without defining foreign keys in MySQL?

Scenario 1 : Foreign keys are defined properly

Scenario 2 : On Deletion and Updation of records, I can ensure that no orphaned data will be left while writing API without defining any FK. Also I will check data integrity while Insertion

So what is the difference between these two scenarios? I just want to know what benefits I will get using FK (quantitative analysis). Will I achieve better performance in Scenario - 1 than 2? I am newbie in MySQL database design.

Upvotes: 0

Views: 43

Answers (1)

Rick James
Rick James

Reputation: 142298

Performance differences...

An FK check must reach into the other table (via an index) to do the integrity check. However...

Situation 1: Simple FK:

In many cases, you can, via understanding the flow of the app code, assure yourself that some FK violations "cannot" happen. For example, when you insert into two tables in a row (and you have checked for errors, etc), and the second table is to point to the first table's row you just inserted, then the FK test is redundant and hurts performance (a little).

If, on the other hand, you "simulate" an FK check by doing an extra SELECT, that would be a noticeable performance hit.

Situation 2: FK with cascading delete:

If you use FKs for "cascading delete" (etc), then this may be more efficient than manually doing the delete.

Further, if you can batch the DELETEs, it is probably faster than letting the cascade do them one by one.

Another comment: "Side effects", such as 'cascading' are considered (by some) to be a naughty coding practice.

But... The above differences are probably not enough to make a difference in your app.

Upvotes: 1

Related Questions