MySQL: More Columns vs. More Queries

Question

So I'm trying to design a rather large database to handle videos and their tags. Because only a finite number of tags can be selected for a video (27) I am storing them in a table 28 columns wide (one column for each tag + the video_id).

My problem arises when I want to get the number of times a user has liked/disliked a video with a certain tag (lets say #24). Each Like/Dislike gets a row in another table.

Here are the two options I've considered.

1) Add 27 more tinyint columns to the User Table, each one representing the number of times he's voted on a video with tagX.
PRO: Easily Selected
CON: Doubles the amount of data needed for each user

2) Use Inner Joins
PRO: Keeps size small
CON: Harder on the system

Both because I'm optomistic and because I'm trying to use good practice, I'm trying to optimize my databases for 300,000+ users, and these numbers will be needed a lot. I Did the math and even with 300,000 users it would only add about 27 megabytes to my database.

What to do!?

Colin Pickard · Accepted Answer · 2011-01-09 08:07:26Z

4

(this is just my opinion)

I think you're optimizing too early (and probably incorrectly). Joins are not that expensive. You should have a users table, a tags table and a like table. Depending on your exact queries, you will probably find this marginally less expensive.

You are better off spending time ensuring you have the right indexes than creating a layout designed for performance over maintainence.

answered Jan 9, 2011 at 8:07

Colin Pickard

46.8k13 gold badges105 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DropDeadFred81 Over a year ago

Thanks for the advice! I'll definitely be doing this

mP. · Accepted Answer · 2011-01-09 08:10:52Z

1

Your denormazling rationality is flawed. Today you might think that 27 videos is the maximum but there will always be another one or two or more in the future.

answered Jan 9, 2011 at 8:10

mP.

18.3k12 gold badges78 silver badges109 bronze badges

Comments

Ronnis · Accepted Answer · 2011-01-09 08:13:14Z

27 columns to user table only work if you have 27 different tags. So, adding another tag requries schema change (which is a bad thing).

I vote for something like this:

table users(
   user_id
  ,primary key(user_id)
);

table videos(
   video_id
  ,primary key(video_id)
);

table tags(
   tag_id
  ,tag_name
  ,primary key(tag_id)
);

table video_tags(
   video_id
  ,tag_id
  ,primary key(video_id, tag_id)
);

table user_likes(
   user_id
  ,video_id
  ,primary key(user_id, video_id)
);

To show the number of votes per tag, you would do something like this:

select c.tag_name
      ,count(*)
  from user_likes a
  join video_tags b uing(video_id)
  join tags       c using(tag_id)
 where a.user_id = ?
 group 
    by c.tag_name;

Collectives™ on Stack Overflow

MySQL: More Columns vs. More Queries

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related