Implementing User Check-in Feature: MySQL vs Redis Bitmap Solutions

Check-in is a common feature in websites and mobile apps to improve user retention, such as awarding points on Weibo or traffic on mobile apps.

Solution 1: Directly store in MySQL

The user table includes fields like last_checkin_time (last sign‑in time) and checkin_count (consecutive sign‑in count).

last_checkin_time = time()
checkin_count = 1

Check‑in Process

1. First sign‑in: set last_checkin_time to current time and checkin_count to 1.

last_checkin_time = time()
checkin_count = checkin_count + 1

2. Subsequent sign‑in on the same day: do nothing and return already signed‑in.

3. Subsequent sign‑in on a new day: if yesterday was signed‑in, increment checkin_count; otherwise reset it to 1.

// Yii implementation
$today_0 = strtotime(date('y-m-d'));
$yesterday_0 = $today_0 - 24*60*60;
$last_checkin_time = $model->last_checkin_time;
if (empty($last_checkin_time)) {
    // first check‑in
    $model->last_checkin_time = time();
    $model->checkin_count = 1;
} else {
    if ($today_0 < $last_checkin_time) {
        return json_encode(['code'=>0,'msg'=>'Already signed in']);
    }
    if ($last_checkin_time < $today_0 && $last_checkin_time > $yesterday_0) {
        $model->last_checkin_time = time();
        $model->checkin_count = $model->checkin_count + 1;
    } else {
        $model->last_checkin_time = time();
        $model->checkin_count = 1;
    }
}
$rs = $model->save();

Solution 2: Redis bitmap

Redis bitmap (supported since 2.2) can store a sign‑in flag per user per day, using SETBIT and GETBIT. The BITCOUNT command quickly counts how many users signed in on a given day.

A key per day with user ID as offset.

A key per user with day‑of‑year as offset.

// Daily key
$key = 'checkin_' . date('ymd');
if ($redis->getbit($key, $uid)) {
    return json_encode(['code'=>0,'msg'=>'Already signed in']);
} else {
    $redis->setbit($key, $uid, 1);
}

// Continuous sign‑in count
public static function getUserCheckinCount($uid){
    $key = 'checkin_' . $uid;
    $index = date('z');
    $n = 0;
    for ($i = $index; $i >= 0; $i--) {
        $bit = Yii::$app->redis->getbit($key, $i);
        if ($bit == 0) break;
        $n++;
    }
    return $n;
}

// Daily sign‑in user count
$key = 'checkin_' . date('ymd');
$count = $redis->BITCOUNT($key);

Pros and Cons Comparison

MySQL : simple logic and easy to implement, but consumes more storage, requires frequent updates, and may need caching for large data volumes.

Redis bitmap : very low memory usage and fast in‑memory operations; however it only stores a single bit per user per day, the offset is limited to 2^32 (~5 × 10⁸ bits), and very large offsets can block the Redis server, so sharding may be needed.

Choose the solution that best fits your performance, storage, and feature requirements.