0

I understand the benefits of using table relationships in order to create constraints, such as preventing a row from being deleted from one table when it is related to a row in another.

But will doing so improve performance when running joined queries.

Eg. suppose I have InnoDB tables USER and USERINFO as shown below:

    USER
   |Field   |Type        |Null  |Key    |Default          |Extra
 --|id      |int(11)     |NO    |PRI    |                 |auto_increment
|  |email   |varchar(700)|NO    |       |                 |
|  |password|varchar(255)|NO    |       |                 |
|  |active  |varchar(1)  |NO    |       |n                |
|  |created |timestamp   |NO    |       |CURRENT_TIMESTAMP|
|
|   USERINFO
|  |Field   |Type        |Null  |Key    |Default          |Extra
 --|user    |int(11)     |NO    |PRI    |                 |
   |name    |varchar(100)|NO    |       |                 |
   |position|varchar(100)|NO    |       |                 |
    ...

Is it beneficial in terms of performance when running the following query? 

SELECT * FROM USER U JOIN USERINFO UI ON U.id = UI.user
andrew
  • 9,313
  • 7
  • 30
  • 61
  • 1
    No. *constraint* itself is nothing more than a constraint code, the real work of "improvement" is done by *index* which you will have to create in order to introduce your constraint. So, if you already have your indixes, then your constraint will not improve your performance (on the opposite - it will a little bit slow you down because of check costs). If you don't have index, then a gain will came from creating *index*, not *constraint* – Alma Do Jul 14 '15 at 15:30
  • Good comment @AlmaDo. When we factor in data less likely to screw up, real performance goes thru the roof – Drew Jul 14 '15 at 15:53

1 Answers1

2

It's an index that might (and most likely will) boost performance, not a foreign key alone.

Also: MySQL creates foreign key indexes automatically for InnoDB.

Community
  • 1
  • 1
Brian
  • 872
  • 8
  • 16