4

Let's say I have a website similar to YouTube where I'm storing and displaying the 'view counts' for each video. It is ok if I'm storing the view count column in the same table as the actual view table - I.e the row which defines the video information? Or should I have a separate table that stores the view count information - I.e having two tables, one that stores the video information such as video Id, title, description, and another table that's only stores the counts, such as having columns: ID, video_id, view_count

The reason I'm asking this is because obviously the view count columns will need to be updated so many times throughout the years - lets say 1million views. I'm wondering if having 'view count' in the same database will have negative performance when it comes down to reading the main video for other users since that column will need to be locked for writing view count so many times throughout the day. Because the video table will be pretty much static in content, and it's pretty much just reads.

Thanks,

  • What is the expected reads per write, and how many rows are we talking about? Also, which RDBMS ? – Max Vernon Nov 2 '15 at 1:19
  • let's say 4 reads per write, but it can vary depending on the traffic coming to the page - 10,000 rows - MYSQL - InnoDB engine. – bravekido Nov 2 '15 at 3:20
  • Does the view count actually need to be real-time accurate? I would have a partitioned/distributed table "viewer X watched video Y" and then roll up the viewed counts on a periodic basis. That assumes that you will have many thousands of simultaneous views. – Jonathan Fite Nov 2 '15 at 20:12
3

It is usually better to have a separate table for "view counts", "likes", "hits", etc. This extra table would have just the id and the counter. The id would match the main table. A missing row in the new table implies 0 (or you could make sure to add a row with 0). Then use LEFT JOIN (or JOIN) when you need the main info plus the count. No auto_increment needed; just PRIMARY KEY(video_id).

Part of the issue is to avoid locks/deadlocks due to constantly bumping the counters versus other activity that does not touch that table.

If you get millions of views/day, then we need to discuss how to keep that from stumbling over itself.

|improve this answer|||||

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.