3 ms·
I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
by mypalmike 2y ago
I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.
- hn_throwaway_99 2y agoI'm surprised there doesn't seem to be more consensus on this issue, but I guess it's because changing the default after the fact would be backwards incompatible. I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.
- RegnisGnaw 2y agoI haven't touched MySQL since the 5.7 says, but I don't think it does. I remember back the creation of the FK will fail if there is no index, but it doesn't create them.
- evanelias 2y agoIn MySQL, the behavior differs between the parent and child sides of the FK. It will auto-create the index on the child side if one is missing. But the parent (referenced) side must already have an appropriate index on the referenced columns. Prior to MySQL 8.4, it was sufficient for the parent table to have any index that begins with the referenced columns. This has become stricter in 8.4 with default settings, to now require a UNIQUE index with the exact referenced columns.
- hu3 2y agohttps://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-key.html https://dev.mysql.com/doc/refman/5.7/en/constraint-foreign-k... Documentation for 5.7 says it does create indexes for FKs automatically if one isn't created. > MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.