0

I'm trying to optimise and speed up a mysql 5.6 datatabase.

I have two tables with a many to many relationship:

Reservation and Resource. Each Reservation can have many Resources and each Resource can have many Reservations. I have a pivot table between the two tables which simply joins the two via their respective Id's

Should I be creating foreign keys within the pivot table for each of the ID's? Will this speed up queries or does this make little difference with a pivot table.

My initial thoughts are that I should but don't want to create a mess!

2

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data.

In most cases, you may want to index the pivot table because the most typical queries that you will be doing are along these lines:

SELECT * 
FROM Reservation RV
JOIN pivot p ON p.reservation_id = RV.id
JOIN Resource RS ON p.resource_id = RS.id
WHERE filter(column_on_Reservation_or_Resource)

In that case, you will traverse the pivot table in Reservation->Resource direction or viceversa. For that, the best index would be pivot(reservation_id, resource_id) (and/or viceversa if you are using it in the other direction), as that way you can get advantage of the covering index optimization. One of the directions can be the primary key, if the pairs happen to be unique (relationships cannot be multiple among the same pair of records from reservation and resource).

  • Thank you - useful explanation which I will look to deploy – Ray Oct 23 '14 at 10:52

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.