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.
I believe you are going to want to have things split out into multiple tables as you've described. Everything in the user table, and don't name it user lest you want to spend your application's lifetime writing [user]
, is going to result in every access to the database to hit one table. Imagine it's the lunch hour and everyone logs in to check their SumanSpace
social network site. .5M users all reading that user table at once and then you have people creating new accounts as well as updating their existing data. The less wide you can make that base table, the better as you're going to be able to stuff more entries in an 8k page.
Plus, things like updates to Education or Location won't need to lock the main user table which could cause contention. Those locks would be on, what I assume, are less frequently accessed tables.
Specific to your tables. Education - I would look to design it as 2 - 3 tables. EducationType
would be a lookup table describing the type of institution (grade school, middle school, junior high, high school, community college, military college, college, university, etc). EducationLocation
to associate an institute of learning with a physical space. Something to consider here are universities with multiple locations like DeVry-is there business value in building out a location hierarchy with the parent in Downer's Grove and then all the satellite campuses listed? EducationObtained
could be another lookup table, indicating the type of education obtained. For things less than the collegiate level, it'd have a value of graduated but the university level could have Bachelors, Masters, PhD, JD, Divinity (however abreviated) in Sciences, Arts, Fine Arts, etc. And of course, a cross reference table tying a user to their tenure (start and stop dates) at an institute.
Finally, you won't want an INNER JOIN
as people may not have or may not choose to record their education or location information.
Those are my top thoughts before I go and get cleaned up. I'm sure @DennyCherry has some insight into the matter based on his time at MySpace.
Best Answer
The engine will only be able to make use of one index per table so while the other indexes may help you in the future with other queries, their benefit will be limited in assisting you with this particular query.
Given what we know about your query and limited knowledge of the table and the insert pattern, I would suggest the following.
Of course, if you were able to limit the result set columns (getting rid of the SELECT *) then further columns in the index could be of benefit.
These indexes above will allow the engine to seek quickly and get quickly to the rows that it wants.
I do have one question about the "merk" on Table B. If that is something like a status that changes then I would not index on it, leaving just an index on ID and Code. If the value of the field changes frequently then it is usually a poor index candidate as it will cause much fragmentation.