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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.