Foreign Keys Performance

[terryds]

Look at my table design below. It is a table that shows the roles of users.

CREATE TABLE userrole (
userid bigint(20) unsigned NOT NULL,
roleid int(10) unsigned NOT NULL,
PRIMARY KEY (userid,roleid),
KEY fk_userrole_roleid (roleid),
CONSTRAINT fk_userrole_userid FOREIGN KEY (userid) REFERENCES user (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_userrole_roleid FOREIGN KEY (roleid) REFERENCES role (role_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Is it better for me to use the foreign keys or not ?
Do foreign keys perform faster than no foreign keys ?
Is it faster using ON DELETE/UPDATE CASCADE than querying it manually ?
When shouldn't I use foreign keys and When I should ?

[Taywin]

From my understanding on your design, your table relation is many-to-many (from how you cascade deleting data from both foreign keys). If so, you SHOULD use foreign keys in order to tie 2 entities in that fashion. If you are doing one-to-many, the only table that has foreign key would be the "many" table (i.e. a user can have many roles, but you do not care for the other way around).

Speaking of performance, it is all depended on how your tables are designed, what they contain, and how you call your query. In general, foreign table should be slower, but there are cases where keeping foreign tables around is faster.

It is faster and much less maintainance issues to use cascade command. You could easily forget to delete manually and that could lead to unexpected result when you update your script. However, make sure that you note that in your documentation, so that future maintenance would take this into the consideration.

When or when not to is, again, depended on your design. Often times, you do not need to use foreign keys if data are tightly belong together (i.e. one-to-one). However, foreign table could be used to reduce the crowded data of a table because often times not all info from a record are needed at once. On the other hand, a record data may not be tightly related but may need all at once every time you use it (and each record is not a big data). In that case, you may combine tables into one even though, by design and theory, they should be separated.

In conclusion, answers to your questions are not black and white especially in practice. If you want theory, you should Google it. In practice, you may have to reconsider again...