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.