CODEWITHSUNDEEP
phpmysqlsql
Azzoth
Azzoth

Reputation: 53

Join or add column?

I have 2 tables users and comments.

In term of performance, should i use a join (with user id) to get the username from users table or should i add a column username to the comments, so i won't need join for only 1 data (username) but i will need to add one more data for each comment (username).

Is join slow if there's a lot of comments in the table ?

Wich one should i do ? Join or add column username to comments.

Thank you.

Upvotes: 0

Views: 1005

Answers (5)

Andy
Andy

Reputation: 128

Personally I would rather user two simple separate queries. I do not like joins all that much. Joins just produce duplicated data by definition. You might want to check http://www.notorm.com/ that is a simple php db access layer going joinless way.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

The one reason why you would store the user name in the comments table is if you wanted to know the user name when the comment was created. If the user name is subsequently changed, you'll still have the name at the time of the comment.

In general, though, you want to use join. You want to have the primary key on a table defined, probably as an auto-incremented (identity) integer value.

If you are concerned about performance for getting all comments for a single user, then you should build an index on the comments table on the user id field.

Upvotes: 0

teynon
teynon

Reputation: 8288

If you're using InnoDB, you can add the column and add foreign key restrictions. This will allow you to increase efficiency and worry less about updating indexes.

Upvotes: 0

sivann
sivann

Reputation: 2131

It depends on the number of users and comments. Having a denormalized db, which is what you ask, can be faster but then you need to take care yourself to update username in both tables. Don't forget to add index for userid in comments table if you go the join way. So the correct answer I believe is go with the join, and denormalize later if needed.

Upvotes: 1

Will P.
Will P.

Reputation: 8787

Join is probably the best so you're not storing data in two places. What if a user name is changed? It won't change the user name for the comments. Also, joining two tables is not very time consuming.

Upvotes: 3

Related Questions