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.

We are using aws/rds, MySQL 5.5.8. all InnoDB.

For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; de-normalized some tables to behave almost like key-value stores; shareded tables containing user generated rows etc.

We are also looking to further improve insert performance by omitting some possible foreign key relationships between large tables (2+MM rows) since our app layer does a lot of similar integrity checking and prevention.

My question is about theoretical join performance between two tables A and B, using a primary key on one side A.F1 and single row index on side B.F2. Since I could also define the B.F2 as a foreign key (over A.F1), I would like to know if such a join would be any "faster/better etc." with or without the foreign key constraints - all other things being equal (indexes, no other fields, no order-by etc.).

We expect a lot of read queries over such situations, and fewer inserts that would benefit from foreign key omissions.

share|improve this question
    
Please provide SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT... FOREIGN KEYs cost. Removing the constraints will help performance. You are worried about performance, but you have not described the issue(s) -- CPU? I/O? SHOW VARIABLES LIKE '%buffer%'; Or see mysql.rjweb.org/doc.php/memory –  Rick James Jun 20 '12 at 22:54
    
I am the original poster. I have no actual issues at this time - we are refactoring the entire data model, and i wanted to know if fkeys per se, help speedup join/queries, since i understand that they do slow down inserts (&updates). irrespective of fkeys, we have indexs over the respective fields. we have decided to implement fkey constraints on all relationships for data managed by the application etc. however, we will omit fkeys on tables/fields that are inserted&updated by users, in real time. btw, i have taken a look at the above link, and found some useful info and tips - thanks a lot! –  user9543 Jun 22 '12 at 2:33

1 Answer 1

Foreign key relationships are to enforce data integrity, not for query performance, that is what indexes are for. Also note that InnoDB creates an index on each column with a foreign key relationship.

However I would recommend having the foreign key relationships to ensure that the data is always valid, especially when updating and deleting, which may become significant when you have to start archiving data.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.