CODEWITHSUNDEEP
phpmysql
bohohasdhfasdf
bohohasdhfasdf

Reputation: 703

inserting and updating constantly. is it better to have one huge table or 2 smaller tables

these tables will be constantly inserted new rows or updating.

i have a big table containing, video url, title, description, tags, views, rating, comments

should i divide this to 2 smaller tables

id video url, title, description

and link this to above.

id video_id, tags, views, rating, comments

Upvotes: 2

Views: 84

Answers (3)

gabrielk
gabrielk

Reputation: 563

The larger your dataset gets, the slower reads, writes and updates are going to be on a single table. You can minimize this by splitting it into as many subsets as seem logical. Take a look at how WordPress stores its tags and comments.

Upvotes: 0

outis
outis

Reputation: 77400

What you're describing is called "vertical partitioning". Whether it's of benefit depends on if you're isolating variable size columns into one table. You can also partition a table horizontally. Read Improving Database Performance with Partitioning for more information on which (if any) partitioning type will help.

As others note, first normalize the data, then denormalize only if you're not getting the performance you need.

Upvotes: 0

zombat
zombat

Reputation: 94167

It doesn't sound like your tables are very normalized. You want to avoid storing duplicate data, unless it has some performance benefit. Duplicating information will lead to larger record and index sizes, which can increase disk seek time.

If the columns you listed are as self-explanatory as they sound, then I would break it into three tables:

  • videos - videoId,url,title,description,rating,views
  • video_tags - videoId,tag
  • video_comments - videoId,comment

This will allow you to have a single entry for each video, storing the primary information just once. You can then relate a video to multiple tags and multiple comments.

Upvotes: 5

Related Questions