MySQL, Foreign Key vs Indexed ID column with NOT NULL

Question

Lets say you have Users table and Posts table.

Users

id
name
email

Posts

id
contents
user_id

If I add index to "user_id" in Posts table, and set it as NOT NULL, Can I expect same effect as Foreign Key?

I know that I can set user_id as any number, whereas foreign_key will force you set valid id. Let's assume that user_id is valid. Is there any performance benefit when we set foreign_key?

Index is the (sub)object in the table structure. NOT NULL is an attribute (property) of the column in the table. Foreign key is a rule for consistency checking subsystem. — Akina, May 10 '22 at 20:28
*Is there any performance benefit when we set foreign_key?* Cannot influence. — Akina, May 10 '22 at 20:30

score 2 · Answer 1 · answered May 10 '22 at 17:59

2

The main benefit of foreign keys is that they enforce data consistency, meaning that they keep the database clean in other words Keys are Indexes that have Integrity rules applied to prevent corruption of data.

Index is a data structure built on columns of a table to speed up search for indexed records based on values of indexed columns. In other words you gain search speed in exchange of insert/delete speed and storage.

Is there any performance benefit when we set foreign_key?

In performance terms, you will face no improvement.

Foreign keys will impact INSERT, UPDATE and DELETE statements because of the data checking rules , but keep in mind that your data will be consistet .

answered May 10 '22 at 17:59

Ergest Basha

7,870
4
8
28

What do you mean `update`? so Say. User ID is updated(changed) from 45 to 78 in users table. Posts Table's user_id will be updated as well? @Ergest Basha – Jin Lim May 12 '22 at 16:19
@JinLim with update I meant when you are performing an update/delete/insert on tables with foreign keys constraint , it will be slightly slower because of the constraint check. As per `User ID is updated(changed) from 45 to 78 in users table. Posts Table's user_id will be updated as well?` It depends , if you add `ON UPDATE CASCADE`. Check for more info https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – Ergest Basha May 12 '22 at 16:46

score 0 · Answer 2 · answered May 11 '22 at 02:06

In MySQL, defining a foreign key constraint automatically creates an index, unless it can use an index that already exists. That is, if you create an index and subsequently add a foreign key on the same column(s), MySQL does not create an extra index just for the foreign key.

If you run a query that needs that index, it doesn't matter if you created the index yourself or if the index was created as a side-effect of adding the foreign key. Either way, the index can help the query. The performance benefit is the same.

If you run a query that does not need that index, then there's no benefit to having index either way.

You didn't describe any specific SQL query, so there's no way for us to guess whether the index is needed.

MySQL, Foreign Key vs Indexed ID column with NOT NULL

2 Answers2