Implementing User Check‑In Feature: MySQL vs Redis Bitmap Approaches

In modern web and mobile development, a user check‑in feature is frequently used to increase engagement, such as rewarding points on social platforms or offering data bundles in apps.

The article explores two typical implementation methods from a technical perspective.

1. Solution 1 – Directly Store in MySQL

A simple MySQL table can record each user's last check‑in time ( last_checkin_time ) and the count of consecutive check‑ins ( checkin_count ).

Check‑in workflow:

Check‑in Process

1. First time check‑in:

last_checkin_time = time()
checkin_count = 1

2. Not first time and already checked in today – do nothing and return already checked‑in.

3. Not first time and not checked in today:

a) Yesterday also checked in:

last_checkin_time = time()
checkin_count = checkin_count + 1

b) Yesterday did not check in:

last_checkin_time = time()
checkin_count = 1

Yii implementation example:

// 0:00 of today
$today_0 = strtotime(date('y-m-d'));
// 0:00 of yesterday
$yesterday_0 = $today_0 - 24*60*60;
$last_checkin_time = $model->last_checkin_time;
if (empty($last_checkin_time)) {
    // first checkin
    $model->last_checkin_time = time();
    $model->checkin_count = 1;
} else {
    if ($today_0 < $last_checkin_time) {
        // already checked in today
        return json_encode(['code' => 0, 'msg' => '已签到成功']);
    }
    // yesterday checked 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 {
        // yesterday not checked in, reset count
        $model->last_checkin_time = time();
        $model->checkin_count = 1;
    }
}
$rs = $model->save();

2. Solution 2 – Redis Bitmap

Redis bitmap (supported since version 2.2) can efficiently record daily check‑ins using a single bit per user.

Advantages include minimal memory usage and fast in‑memory operations; the bitcount command can quickly count how many users signed in on a given day.

Implementation steps:

Use a daily key (e.g., checkin_20231127 ) where each user ID is an offset.

Use a per‑user key where each day of the year is an offset, allowing a full‑year record in only 365 bits.

Sample code for daily check‑in:

// daily key
$key = 'checkin_' . date('ymd');
if ($redis->getbit($key, $uid)) {
    // already checked in
    return json_encode(['code' => 0, 'msg' => '已签到成功']);
} else {
    // check in
    $redis->setbit($key, $uid, 1);
    $redis->setbit('checkin_' . $uid, date('z'), 1);
}

Code to calculate a user's consecutive check‑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;
}

Code to count how many users checked in on a specific day:

$key = 'checkin_' . date('ymd');
$redis = Yii::$app->redis;
$count = $redis->BITCOUNT($key);

3. Pros and Cons Comparison

MySQL Direct Storage

Pros: Simple concept, easy to implement.

Cons: Consumes more storage, frequent table updates can affect performance, and large data volumes may require caching.

Redis Bitmap

Pros: Very low memory footprint, pure in‑memory operations, high speed.

Cons: Stores only a single bit per user per day, limited information; offset cannot exceed 2^32 (≈5×10^8 bits), which is sufficient for most cases but may require sharding for extremely large scales.

Both methods have their own trade‑offs; choose the one that best fits the project's performance and data requirements.