1

Possible Duplicate:
Does Foreign Key improve query performance?

I am working with tables that have no relationships (yet the data is related). I am throwing JOIN queries between these tables. Would it improve performance if I add the relationships? (PS -I will add the relationships in the future for data integrity, right now I am working on the performance. I appreciate your help.)

Community
  • 1
  • 1
  • I don't believe having or not having foreign keys defined does much for performance. You should have them to ensure data integrity. – Oded Dec 21 '12 at 15:42

2 Answers2

5

The relationship itself wouldn't help performance, however the relationship implies that there will be a unique index on the parent table columns. That index may affect your performance, depending on the queries you run.

BStateham
  • 1,609
  • 10
  • 15
  • 1
    THat pretty much is it. Relationship or not is irrelevant for the query optimizer - but "relationhip = index" or - "no relationship = index MAY not be there". In which case the missing INDEX may make things slow. – TomTom Dec 21 '12 at 15:45
  • 1
    This is not true. The optimizer can and does use FK relationships to make decisions about query plans. Depending on the shape and size of your data, a relationship can have a significant impact. – DeanGC Dec 21 '12 at 16:57
2

Missing relationships between tables can have a big impact on performance, particularly as tables get large. The optimizer is a complex piece of software that needs every bit of information it can get when making decisions, and the fact that a defined relationship exists between tables is an important fact.

For an example, see here.

DeanGC
  • 670
  • 5
  • 7