Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.
In order to retrieve the data I have to use Left Join query. Doesn't that make the whole process longer to retrieve rather than having it all in one table? Eg I could have the location as text on the same table.
EDIT: Here is an example.
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `gender` enum('M','F') DEFAULT NULL ); CREATE TABLE `user_location` ( `user_id` int(11) NOT NULL, `location_id` int(11) NOT NULL ); CREATE TABLE `location` ( `id` int(11) NOT NULL, `location` varchar(45), `parent_id` varchar(45) );
Note: Please assume that all related fields are properly indexed between them.
Edit: I currently have a large database with users that retrieve their location via a junction table as described above. I was asked to optimize the database because the search results are slow. I've added
memcache and it improved significantly but now I am just wondering about Left Joins.
For example, The current query is something like that:
SELECT * FROM users LEFT JOIN user_location ON user_location.user_id = user.id LEFT JOIN location ON location.id = user_location.location_id;
And that is just to get the location. They have several other fields that are retrieved through junctions and they are all needed to view a user's profile. We have phone numbers, addresses, passwords, D.O.B and many others all in different tables.
In order for me to create a page for the user profile I have to send the server a large query. Now after the first time it gets cached and it's fine. But I was just wondering why would someone build their database like that?