5

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.

driscan
  • 53
  • 1
  • 1
  • 3

2 Answers2

6

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.

Vérace
  • 29,825
  • 9
  • 70
  • 84
  • This does not answer the question "do you think that adding a foreign key in table T to Q would cause a significant performance impact." – Sloganho Jun 11 '19 at 17:11
  • The index on the T table will increase lookup speed at the price of a small hit for inserts. Maybe I should have suggested that the OP perform some tests to quantify the performance gains (lookups) and penalties (inserts) but I believe that the question is answered - only the OP has access to their own data to provide exact metrics for their use case. I would just say that IMHO it is worth paying a high performance price in order to maintain (Declarative Referential) data Dntegrity where the data model warrants it. DRI is best maintained in the RDBMS, not the app! – Vérace Jun 19 '19 at 07:15
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?"

Neil McGuigan
  • 8,423
  • 4
  • 39
  • 56