1

See this scenario

Table: "user" - Columns: id (primary key/unsigned int), email (unique/varchar), name(varchar)

Now we also have a "order" table and two options of foreign key:

  1. Foreign key order.user_email + user.email
  2. Foreign key order.user_id + user.id

If we choose the first options instead of the second, are we going to have a negative performance impact (because the type of columns involved in the foreign key)?

Improve this question
4
  • 4
    Does this answer your question? Is a surrogate key better than a natural key in this case
    – mustaccio
    Commented Jul 7, 2021 at 17:03
  • I would like an answer more related to Mysql behavior
    – Gnu
    Commented Jul 7, 2021 at 17:19
  • Why do you think that MySQL behaviour is different from other relational databases?
    – mustaccio
    Commented Jul 7, 2021 at 17:20
  • Because different databases can have different implementations of same abstraction that can cause different levels of positive/negative performance impact.
    – Gnu
    Commented Jul 7, 2021 at 18:02

1 Answer 1

Reset to default
0

A FK provides 2 things:

  • An integrity check. This means that an INSERT will verify that the corresponding row in some other table already exists. This takes a small amount of time

  • An INDEX. An index is automatically added if there is not one already defined. You probably need this index for other queries, so the performance hit for this is effectively zero.

Do the math. How often are SQL queries being run? If only 100 queries per second, you may have trouble measuring the CPU meter. If 1000 per second, you may have issues. I suspect that your app won't be bothered by the FK.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.