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.
I'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.
I 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.
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.
In 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.
I'm pretty sure MySQL creates fk indexes by default, but I believe MS SQL Server does not, like Postgres.