Designing Scalable Like/Comment Systems: MySQL vs Redis Strategies

Likes, comments, and favorites are fundamental features in modern app development, and this article examines the database design challenges for these scenarios.

1. Requirements

Display the number of likes.

Determine whether a user has already liked an item to prevent duplicate likes.

Show a personal list of liked items, typically in a user profile.

Display the list of users who liked a specific article.

2. MySQL solution

Although NoSQL databases are popular, MySQL remains indispensable for most small‑to‑medium projects (data under ten million rows). A simple sharding + cache approach can handle the load efficiently.

-- article table
create table post {
    post_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT 'like count'
}

-- user table
create table user {
    user_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT 'like count'
}

-- like table
create table star {
    id int(11) NOT NULL AUTO_INCREMENT,
    post_id,
    user_id,
    ...
}

Common queries:

Query posts liked by a user: select post_id from star where user_id=? Query users who liked a post: select user_id from star where post_id=? The like count can be periodically aggregated and stored back into the post and user tables. This design works well for modest data volumes, but when the table grows large, a single table becomes a bottleneck and may require sharding, which introduces redundancy and consistency challenges.

2.2 Redis solution

When data reaches hundreds of millions, caching becomes essential. Redis is often chosen because of its rich data types that suit various scenarios. It can be used as a pure cache in front of MySQL or as a storage layer combined with MySQL.

Scenario A – Display like count

Store the like count as a simple string keyed by the article ID.

# set initial count
127.0.0.1:6379> set star:tid:888 898
# increment count atomically
127.0.0.1:6379> incr star:tid:888
(integer) 899

Scenario B – De‑duplication (prevent duplicate likes)

Maintain a set of user IDs for each article.

# add user IDs to the set
127.0.0.1:6379> sadd star:list:tid:888 123 456 789
(integer) 3
# check if a user has liked
127.0.0.1:6379> sismember star:list:tid:888 456
(integer) 1

This set can also be used to generate a user's personal like list (Scenario C) and the article's like list (Scenario D). The UI typically shows a red thumb if the user has liked, otherwise a gray one.

3. Data consistency

When Redis is used as a storage layer, persistence must be enabled (both RDB and AOF) to avoid data loss, which consumes a significant portion of memory and requires careful capacity monitoring and scaling. Replication introduces consistency challenges that need further discussion.