2

I have a MySQL/MariaDB database where posts are stored. Each post has some statistical counters such as the number of times the post has been viewed for the current day, the total number of views, number of likes and dislikes. For now, I plan to have all of the counter columns updated in real-time every time an action happens - a post gets a view, a like or a dislike. That means that the post_stats table will get updated all the time while the posts table will rarely be updated and will only be read most of the time.

The table schema is as follows:
posts(post_id, author_id, title, slug, content, created_at, updated_at)
post_stats(post_id, total_views, total_views_today, total_likes, total_dislikes)

The two tables are connected with a post_id foreign key. Currently, both tables use InnoDB. The data from both tables will be always queried together to be able to show a post with its counters, so this means there will be an INNER JOIN used all the time. The stats are updated right after reading them (every page view).

My questions are:

  1. For best performance when the tables grow, should I combine the two tables into one since the columns in post_status are directly related to the post entries, or should I keep the counter/summary table separate from the main posts table?
  2. For best performance when the tables grow, should I use MyISAM for the posts table as I can imagine that MyISAM can be more efficient at reads while InnoDB at inserts?

This problem is general for this database and also applies to other tables in the same database such as users (counters such as the total number views of their posts, the total number of comments written by them, the total number of posts written by them, etc.) and categories (the number of posts in that category, etc.).

P.S. The views per day counters are reset once daily at midnight with a cron job. One reason for having posts and post_stats as two tables is concerns about caching.

1
  • 1
    A separate table is a reasonable strategy, but keep all tables innodb. Reliability is more important than imagined efficiency.
    – danblack
    Mar 3, 2022 at 12:21

1 Answer 1

0

I would consider separate tables. Think about all of the data analysis you may ever need to do in the future, and plan a schema that supports that. With single counts in the post_stats table, you'll only ever know a post was viewed x time per day, or y times overall, etc..

Instead, consider dropping that table all together, and creating a tables called Views and Likes. For example. the Views table could be foreign keyed to the posts table. In this table you could store details such as...

  • view_id
  • post_id
  • viewed_by
  • datetime_viewed

With this schema, you could query total views by post or by user, and for any time period you choose. For example...

  • views by post for today
  • views by user for the past week
  • views of user's posts for the past week
  • etc..

You could then create various views to return stats from those tables, as you see the need. Going with this strategy, you'll will not be limited on what you can analyze later.

Additionally, it would would just be a simple insert anytime an action happens, vs an update that may conflict with another simultaneous update.

The same concept would apply for Like, etc..

10
  • 1
    Additional, if the like counter is stored in the SAME table as the post, then the transaction log entry for updating just the like count must also contain the post, unnecessarily requiring more resources just to increment an integer. Definite best practice to keep small volatile pieces of data away from largely static ones. Mar 3, 2022 at 13:46
  • @JonathanFite great point! Mar 3, 2022 at 13:48
  • @BrendanMcCaffrey I already have a table for votes that stores information about each user who votes, the post, the vote, and the time of the vote, similarly to the schema you described. total_likes and total_dislikes were just for avoiding COUNT(*) operations that would be done every time a user views a post in order to show its vote count (i.e. precalculating in addition to the votes table). I know this requires extra maintenance but decided to do it because of some COUNT(*) performance bottlenecks in the past (it might have been improper indexes but I am not sure).
    – kataba
    Mar 3, 2022 at 14:41
  • @BrendanMcCaffrey For the post views, I did not envision storing more than the view counts as the only information that was needed in the long term was the total view count, because I expected that the table would grow too much very quickly on a high-traffic website (bots, etc). However, I like this solution and after reading it I could think of some interesting use cases in the future. One issue I am facing is that I also have to consider storing anonymous views (users who are not logged in) which would mean either having 2 tables - one storing IPs the other user IDs, or having both in one.
    – kataba
    Mar 3, 2022 at 14:42
  • 1
    @BrendanMcCaffrey I tried upvoting but I cannot because my reputation is still low (new account): "Thanks for the feedback! You need at least 15 reputation to cast a vote, but your feedback has been recorded."
    – kataba
    Mar 4, 2022 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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