Performance impact of adding a foreign key to a 1M rows table

Question

I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q).

I'd like to make a connection between T and Q by adding a foreign key on T referencing Q.

Considering that Q has over 1 million rows (constantly growing) and T about 100k, do you think that adding a foreign key in table T to Q would cause a significant performance impact during daily operation? The alternative would be to add a simple INT column without a foreign key. Or anything else, I'm open to suggestions :)

For the details :

• T has about 20 rows, mainly (SHORT) INTEGERs, some other foreign keys and a couple DateTime fields
• Q has 2 SHORTINT fields and a couple VARCHAR fields (255b each).

Thanks in advance for your insights.

Answer 1

You are far better off maintaining a foreign key (FK) constraint if your data warrants it.

MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be the PRIMARY KEY (PK) - from here).

See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase lookup speed at the price of a small hit for inserts.

Believe me, you will save yourself an immense amount of grief by making use of the database capabilities rather than trying to do this yourself.

Answer 2

That's not how it works. Do it correctly first, then optimize.

Use a foreign key if you need a foreign key.

Then measure performance. Find the hot spots. Optimize those. If the foreign key happens to be large performance drag (doubtful) then remove it.

Also, you didn't ask "What will be the impact of having incorrect data?"