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

database-designforeign keyinnodbMySQL

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.

Best Answer

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.