0

I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for views count as below:

views:
id,
blog_id,
ip_address,
counter

Now I'm storing unique visits in views table. And when I save record in view table I also update blog field views_count field, so is this a good approach? Or is there a better alternative?

Full Creates Schema:

CREATE TABLE `video_blog` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `sub_title` varchar(255) DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `video_embed_code` text,
  `video_thumbnail` varchar(255) DEFAULT NULL,
  `video_thumbnail_alt` varchar(255) DEFAULT NULL,
  `description` text,
  `views` int(11) UNSIGNED NOT NULL,
  `is_active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_category`

CREATE TABLE `video_blog_category` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `order_by` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Table structure for table `video_blog_views_tracker`

CREATE TABLE `video_blog_views_tracker` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `video_blog_id` int(11) UNSIGNED DEFAULT NULL,
  `user_ip_address` varchar(255) DEFAULT NULL,
  `counter` int(11) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Note: Our website blog is getting millions of visitors daily. So the new table will get updated frequently.

  • It is 'data override' and is a bad practice in common. The best way is do not store this data obtaining it by query SELECT blog_id, SUM(counter) FROM views GROUP BY blog_id when it needs. And when I save record in view table I also update blog field views_count field And what the difference - to update this field directly or with intermediate insert into views? – Akina Dec 7 '18 at 11:34
  • @Akina Actually I will fetch views count in each blog post, so it will be better to also store it in blog views_count field. Otherwise at each blog page i will be querying the views table which is an extra request. BUT i think both cost a request! – DojoDev Dec 7 '18 at 12:26
  • I will fetch views count in each blog post If so data override can be reasonable. Especially taking into account that a small inaccuracy of this data does not matter. Just additionally provide periodic execution of the procedure which recalculates this data. – Akina Dec 7 '18 at 12:34
  • @Akina Actually I'm creating new fresh blog functionality and I am also creating this counter in this. In our old functionality we are having a blog table and blog_tracking table which are causing performance issue. And the issue is because there are million of visitors each day and blog_tracking table is updated with each unique view. So i want a better solution to handle this issue in my rewrite. – DojoDev Dec 7 '18 at 12:38
  • I think that trigger logic with single-row update will not be too expensive... – Akina Dec 7 '18 at 12:44
0

At a mere 10 updates per second, this is a non-issue.

When you get to 100/sec, and are still using HDD, then we can discuss further. Or 1000/sec with SDD.

At higher rates, yes, violate the textbook principles and have the view-counter in a separate table (with only the count, plus page_id as the PK count). The reason is to avoid conflicts with non-counter accesses to the main table.

If you are keeping track of each 'view' as in a table of "who viewed what, when", the problem gets messier. On the one hand, there are INSERTs into that table (again, 10/sec is not a problem). On the other hand, SELECT COUNT(*) ... will have extremes -- a count of 100 is no problem, but a count of a million can be.

"Likes" have similar issues.

For more extreme traffic, you need to gather up the updates/inserts, consolidate them, then apply them. This might get you another 10x speedup, at the expense of some complexity and a few seconds delay in updating the counters.

But, by that time, you will have outgrown a single server, and other solutions will be needed for all your problems. Sharding is a likely part of this next level of design.

For any system that starts small, and grows to be huge, you must expect to do a major redesign every so often. For you (today), moving the counters out is premature. However, doing so could forestall (for a while) the next major redesign.

Rehash

Plan A: (all in one)

CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
);

Plan B: (split out just the counter)

CREATE TABLE Blog (
    id INT UNSIGNED AUTO_INCREMENT,
    lots of meta info -- title, etc
    PRIMARY KEY (id)
);
CREATE TABLE BlogViews (
    blog_id INT UNSIGNED,   -- not A_I; for joining to Blog
    view_ct INT UNSIGNED NOT NULL DEFAULT '0',
    ts TIMESTAMP NOT NULL,   -- optional -- time of last viewing??
    PRIMARY KEY(blog_id)
);

Discussion of Plan A:

  • Simpler
  • Good enough for "low traffic" website -- say 10 views/sec.

Advantages of B:

  • Needs JOIN, but only when both meta and count are needed together. This JOIN is NOT a big burden.
  • Updating the count hits only BlogViews, thereby not interfering with any queries that need only meta info, especially UPDATEs to such.
  • Needed for busy website -- say peak loading of 1000 views/sec.

When to use C:

  • Thousands of views/sec.
  • C involves collecting views, consolidating them, then updating a structure like Plan B.
  • This further isolates both Blogs and BlogViews from interference.
  • View counts may be slightly delayed (seconds).
  • (Further details can be discussed elsewhere)

Plans A2, B2, C2, D2:

  • These are modifications to the other plans, wherein you keep track of 'who' views the blogs 'when'.
  • This needs to worry about SELECT COUNT(*) instead of merely SELECT view_ct.
  • SELECT COUNT(*) can be costly if you have a million views.
  • These extensions are best handled with "Summary Table" design concepts, which I cover here.

Plan E (Now that the actual schemas are presented, I'll call that E):

For video_blog_views_tracker, get rid of id, and have

PRIMARY KEY(video_blog_id, user_ip_address)  -- should be unique

That should be optimal for the counter query:

SELECT SUM(counter) FROM video_blog_views_tracker
    WHERE video_blog_id = ?

Yes, rolling that into video_blog.views via a TRIGGER or CRON job is possible. But I would not do it until the need is determined.

  • I have created blog_views_counter table I have created id, video_blog_id, and count field. And in main video_blog table I have a field called views_counter for storing static value. Now I'm using the separate table blog_views_counter for storing the new entries or updating old, and also I'm updating views_counter field in video_blog. And in main blog page I'm accessing total views from video_blog field views_counter field. How is this approach? – DojoDev Dec 8 '18 at 8:00
  • I'm not trying to argue that 1 million updates in a day isn't necessarily a lot... But I don't think it's safe to assume that 1 million/day = 11/second. Peak load (ex mid day for OP's core geographic market) may easily be 10x or more. Unsure of @DojoDev's needs, but it may be more appropriate to plan for that peak, rather than an average. – AMtwo Dec 8 '18 at 13:49
  • @AMtwo - Sure, peak loading is important. I use the Rule of Thumb: "Under 100 QPS is probably not a problem". 11/sec * 10x ~= 100. – Rick James Dec 8 '18 at 20:08
  • @DojoDev - There is (I think) no need for id; video_blog_id is a better PRIMARY KEY for that counter table. When will you copy the counter into video_blog table? And why bother?? A simple JOIN let's you get the counter in the few queries that need it. – Rick James Dec 8 '18 at 20:11
  • @RickJames Please check this snap. ( imgur.com/aRGcIww ) I'm following this approach. As i mentioned i will be having millions of hits daily so updating video_blog table isn't a good idea, so i just update the views table and the time i update the views table I will then update video_blog count field. Along with that I will be able to fetch views from video_blog without JOIN. – DojoDev Dec 9 '18 at 6:58

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.