Foreign Keys VS Joins

Foreign Keys vs Joins

Foreign keys are just constraints to enforce referential integrity. You will still need to use JOINs to build your queries.

Foreign keys guarantee that a row in a table order_details with a field order_id referencing an orders table will never have an order_id value that doesn't exist in the orders table. Foreign keys aren't required to have a working relational database (in fact MySQL's default storage engine doesn't support FKs), but they are definitely essential to avoid broken relationships and orphan rows (ie. referential integrity).

JOIN read performance without foreign keys vs join with foreign keys

Foreign key constraints are used for integrity, to ensure that one column's values are a subset of another's. Provided that your columns are indexed either way, FK constraints will make no difference to read performance. They will, however, affect insert/update/delete performance.

Does using Foreign Key speed up table joins

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.

Is there a performance difference between joining on two indexed tables vs a foreign key?

It looks like performance gains only come from adding an index; not from the foreign key itself (i.e. the foreign key only provides referential integrity).

http://postgresql.1045698.n5.nabble.com/indexes-on-primary-and-foreign-keys-td2054279.html

You'll probably take a slight performance hit on inserts (and updates involving the foreign key field), as the system will need to validate that the item exists in the referenced table.

Foreign Keys VS Joins