4

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.

Improve this question
2
  • 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, 2012 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, 2012 at 2:33

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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