Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which version of MySQL are you using? –  ypercube Dec 20 '13 at 23:05
    
mysql version is 5.1.72 –  greenone83 Dec 21 '13 at 23:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.