3

I think my title is pretty self explainitory, however I will outline what I am doing below.

A person is connected to a town. This town is connected to a county. The county is connected to a country. There are many counties in one country. Also there are many towns in a county. That being said many people can be from the same town.

In my database I have a separate table for people, towns, counties and countries. To get a full user record I LEFT JOIN the tables on a record set. This is a simplied version of what I do as a singular person is spread across many more tables. Would the use of FOREIGN keys speed up the execution of my query? Even if it is only a slight speed increase or less resource usage happening I would be interested.

The scale at which I am planning for is quite large, so the execution time and resource usage could be useful. As on a single person coming back from the database I have roughly 5-6 joins for multiple tables.

edit for the answer so far I would like to point out I am indexing each table, using primary and unique keys. Also the data set could quickly add up to millions of rows in one table(people) so performance is key.

JustSteveKing
  • 968
  • 1
  • 10
  • 29

3 Answers3

6

Foreign keys do not directly speed up the execution of queries. They do have an indirect effect, because they guarantee that the referenced column is indexed. And the index will have an impact on performance.

As you describe the problem, all the join relationships should include the primary key on one of the tables. The resulting queries should be a very efficient in execution.

I would not worry about 5 or 6 joins for the queries -- unless you have a very large amount of data (more than one table with millions of rows). Or you are in a severely memory-constrained environment.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Anytime you add a foreign key, you really should index the key, so that at the very least will speed up your queries, however beyond that they are not designed to improve performance. It is, however a good idea to use foreign keys because they ensure the integrity of your data. Indexing the keys is the piece that will speed it up, but you should still use foreign keys anyway.

nomistic
  • 2,902
  • 4
  • 20
  • 36
1

Generally speaking, a foreign key is used for data integrity (aka referential integrity) to ensure the field with the foreign key actually references a record in another table. As such, while it won't have much impact on query performance it will decrease insert performance just slightly (since there's a check that must be performed on another table during the insert).

For speeding up query performance you should use indexes. But note that while indexes will significantly speed up queries on large data sets it will also (yet again) decrease performance of inserting records just slightly.

neuronaut
  • 2,689
  • 18
  • 24