I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance join-performance so I took the time two create a second schema where fks are added.
usually a join in the app/orm looks like this:
SELECT <fields...> FROM a INNER JOIN b ON a.property = b.pk INNER JOIN c ON a.property = c.pk INNER JOIN d ON c.property = d.pk WHERE <something>
Each property that references a pk in another table always comes with an index but in the new additional schema an fk is also added (which, as i understand is formost a constraint).
Now I ran tests to measure the speed between schema a and b and could find no differences whatsoever. also whenever I use EXPLAIN on a statement it shows up exactly the same in both schemata.
am i missing something or is the conclusion valid that fks do in fact not enhance select-performance if both property and pk in the join-condition are indexed anyway?
what could even be a possible advantage of the fk? the fact that the db knows that the value must exist?
is it true that mysql will attempt to put related data close together? and a foreign key will help mysql understand what is related?