How much do foreign keys love resources?

Question

MySQL

I decided to start using them to the fullest, but I'm worried if I'm not carried away with them, I understand when it's important to maintain the integrity of important data, etc., but let's say I have almost all models that store user id's that have been created, changed, etc. . how important is it? those. for 5 years of experience, no one seems to have asked such information and I didn’t seem to look myself, I’m not too lazy to set foreign keys, but how much will this affect resources? the sites are still simple, there is almost no load, so there is not much experience. In general, I will be glad to hear a bad experience with foreign keys, before that I kind of lived without them, but maybe I was just lucky

Answer 1

Making foreign keys is mandatory.
And put down unique indexes. Another thing would be to set a check constraint, but you have stupid mysql in tags, it doesn’t know how.
These are all your helpers. They help to find mistakes and perfectly interfere with doing stupid things.
And choose the appropriate data types. Well, since you have mysql, then by all means check sql_mode and set it as aggressive as possible. In 5.7 it became much better, before that, out of the box, I allowed too much to do stupid things.
If you grow up to a terabyte base like Avito, then you can start thinking about how much money the integrity of foreign keys costs us, or it will be cheaper to periodically check the integrity with scripts and puzzle developers. Oh, by the way, here is the recording of the talk from pgday15, at the 27th minute, the discussion of the question from the audience "guys, are you in your mind to turn off FK?"

Answer 2

See what resources. A foreign key, or rather an index on a foreign key field, speeds up table lookups. Those. when you make a query, a complete iteration of the table does not occur. The DBMS on an index of this table at once takes the necessary lines. Hence - less consumption of processor time, but more disk consumption, because. index is also a file.

Answer 3

Or
Or
Those. still don't quite understand.
In fact, it all depends on the business processes that you implement.
It is hardly necessary to store all visitors to a public toilet for 10 years.
But in the context of some supermarket chain, it is already possible to store all visitors. And do analysis on them.
The same goes for internal processes. In the general case, it is better to record the actions of operators in order to find out the reasons for certain events (for example, the cargo went to the wrong addressee - why?)
As for foreign keys, they are a little different. They guarantee you the integrity of the data in the context of the database.
Those. you will not be able to delete a user who has a history of orders, visits, etc.
The reverse is also true, you cannot add a visit to someone who is not in the user table.

Answer 4

Decided to start using them to the fullest

Remember the saying about the one who breaks his forehead?
You need to use it not "to the fullest", but "as needed".
Any index slows down the insertion (not so much, but for a million records it is noticeable), and spends not so much disk (this is garbage), but memory (which is also inexpensive, but you just need to buy it).
But indexes often speed up sampling by 10/100 times.
Accordingly, if you have a regular selection of the "all orders of the user" type, then it is better (necessary) to create an index for the "user".
Note that I only describe indexes, but FK is not only indexes, it is also integrity control, which is also a resource (there is also a benefit, but not always, and in simple applications like an online store it is rather meaningless).