6

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?

  • Can you edit your question and add the CREATE TABLE script? – oNare Jul 6 '15 at 2:11
  • Are you writing your database in assembler since you only care about performance? – Neil McGuigan Jul 6 '15 at 3:37
  • 1
    I think you are misusing the term "Left Join". This has a very specific meaning when SQL is being discussed, and Left Join is completely extraneous to the problem as you have presented it. If this is part of a larger problem, and the larger problem requires a left join, then please rephrase your question so we can understand the larger problem. – Walter Mitty Jul 6 '15 at 10:14
  • The query you show will get all users' profiles, not for just one user. Do you really need to get the profiles of all users often? – ypercubeᵀᴹ Jul 6 '15 at 12:06
8

If you put everything in one table, you will have a bigger, redundant table.

If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.

  • 2
    @Namphibian: "A Left join means that all rows from the LEFT side is read" That is completely wrong. "left join" is an element of SQL and SQL is a language the describes the desired result of your query but it does not describe how you retrieve the result data. The software component that actually decides how to retrive the data is called 'query optimizer' in many database. If all the data needed is already contained in the index, an optimizer can decide to scan only the index and not the table. – miracle173 Jul 6 '15 at 7:17
  • 2
    @Namphibian: You mean even if I'm retrieving only the indexed columns from the table, MySQL will still have to touch the table? Is that how non-clustered indices work in MySQL? – Andriy M Jul 6 '15 at 9:57
  • 1
    @Namphibian: I didn't see the mysql tag and was head the Oracle database in my mind when I was writing my comment. Maybe mysql does not use indices in the way i described (at least until now, maybe in later versions) But I am surprised that a mysql index does not contain data of the indexed columns and but only pointers to the table rows. – miracle173 Jul 6 '15 at 10:06
  • 1
    I wouldn't call the primary key "pointers" but ok. Still, your previous comment said "you are just reading pointers to the data on the table and not the actual data" and this not accurate. Reading an index you are (maybe) not reading all data but you are still reading data. – ypercubeᵀᴹ Jul 6 '15 at 11:06
  • 1
    And you don't need to have all the columns in an index to be used. Not all queries have SELECT *. – ypercubeᵀᴹ Jul 6 '15 at 11:08
7

Junction tables are a very standard practice in relational database design. It's covered in database 101. If you have a many-to-many relationship between two entities, the standard way to represent them is with three tables.

Two of the tables are entity tables, with a primary key. A junction table lies between them (logically) and contains two foreign keys, one that references each entity table. Often, these two foreign keys will be the only two columns in the junction table.

I can't understand why anyone would ask whether or not this is a good practice, unless they have never covered database 101.

4

"Please assume that all related fields are properly indexed between them." No, I won't do that. I see too many users who have never heard of "composite" indexes, much less understand their importance.

In particular, you should have:

CREATE TABLE user_location(
    # No surrogate id for this table
    user_id     MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    location_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(user_id, location_id),            -- When starting with user
    INDEX      (location_id, user_id)             -- When starting with location
) ENGINE=InnoDB;

Further notes are in my blog.

1

Your approach with DB is wrong. A table is not a bunch of fields to hold data that you handle adding/removing columns without criteria. DB structure is the result of a analysis. This part of Db born from specific requirements: A user live in one or more location. In the same location can live one or more user. A user is identified by a name and gender. A Location is identified by id. Based on this requirements you identify 2 entities: Users And Locations. Since the association between those entities is a many To many, transforming the conceptual schema to ER, you will (matematically) obtain a specific table that regards UsersLocations, composed (at least) by two foreign keys that points to both entities. Since the name can't be used as primary Key (because People can have the same name), you use an ID (probably with an autoincrement).

0

If you have an EAV you just have to INSERT a default value of location_id = 0 or 1 and it description would be Undefined or Not Set in your locations table. Make a trigger that INSERT by default in the table with the user_id and location_id.

So, you don't need to use LEFT JOIN and make the search slowly, just a JOIN. If the user has the location_id= 0 or 1 (what you took) is going to return the default location_name.

By the way, the LEFT JOIN syntax will depend of your index. If you have an index on those fields, I don't see the problem if your users table isn't big (Assuming).

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.