3 ms·
A better post than I expected. The only thing I'd add is that foreign keys can actually improve read times because the optimiser knows it can safely skip certai
by _a_a_a_ 3y ago
A better post than I expected. The only thing I'd add is that foreign keys can actually improve read times because the optimiser knows it can safely skip certain joins e.g. if you have inner equi-joins between tables a, b and c
a join b join c
If there is an FK from a to b, and likewise from b to c, and you don't use anything in b, then the optimiser can rewrite this to
a join c
YMMV
- senderista 3y agoYes, I remember using foreign key relationships to optimize symmetric hash join back when I worked on an OLAP db. The idea was that for a 1:1 relationship you can immediately discard both sides of a joined tuple, while for a 1:many relationship you can immediately discard the "many" side of a joined tuple.
- _a_a_a_ 3y agothanks for the detail!