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,

Improve this question
3
  • What is the expected reads per write, and how many rows are we talking about? Also, which RDBMS ?
    – Hannah Vernon ♦
    Nov 2, 2015 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, 2015 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, 2015 at 20:12

1 Answer 1

Reset to default
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 and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.