4 ms·
Why are indexes on foreign keys required? If I'm doing a join, it's going to select the primary key of the other table, how will an index on the foreign key hel
by RedShift1 11mo ago
Why are indexes on foreign keys required? If I'm doing a join, it's going to select the primary key of the other table, how will an index on the foreign key help?
- formerly_proven 11mo agoReferential integrity checks by the DB engine (e.g. when deleting from the foreign table) require reverse look-ups of foreign keys, which would necessarily become full table scans without an index. Apart from that, applications also often do look-ups like this.
- 1a527dd5 11mo agoIf you care about performance, they are required. https://dev.to/jbranchaud/beware-the-missing-foreign-key-index-a-postgres-performance-gotcha-3d5i https://dev.to/jbranchaud/beware-the-missing-foreign-key-ind... Again, another thing we learnt the hard way. All FKs now require a index for us.
- magicalhippo 11mo agoI'm very curios what sort of work you do with databases to even ask this question. Clearly you don't have the kind of "load all invoice lines belonging to this invoice" type workloads that I'm used to. Is it all OLAP?
- RedShift1 11mo agoMost of my experience is with MySQL/MariaDB which created foreign key indexes behind your back.