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

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.

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.

StackExchange Database Administrators Q#105469, answer score: 6