User Logs Pagination: Implement Efficient Keyset Pagination Over a User Activity Log

Question

## Problem

You have a `user_logs` table with millions of rows. Implement pagination using keyset (cursor) pagination, which is more efficient than OFFSET for large datasets.

**Schema:**
```sql
CREATE TABLE user_logs (
  log_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id   BIGINT NOT NULL,
  action    VARCHAR(64),
  logged_at TIMESTAMP NOT NULL,
  INDEX idx_user_logged (user_id, logged_at DESC, log_id DESC)
);
```

**API:** `GET /users/:id/logs?cursor=<token>&limit=20`

Write the SQL for the first page and subsequent pages.

```sql
-- First page
SELECT * FROM user_logs
WHERE user_id = ?
ORDER BY logged_at DESC, log_id DESC
LIMIT 20;

-- Next page (cursor encodes last_logged_at and last_log_id)
SELECT * FROM user_logs
WHERE user_id = ?
  AND (logged_at, log_id) < (?, ?)
ORDER BY logged_at DESC, log_id DESC
LIMIT 20;
```

## Follow-ups

1. Why is `(logged_at, log_id) < (?, ?)` correct even when two logs share the same timestamp?
2. Compare OFFSET vs. keyset pagination for the 10,000th page of results.
3. How would you encode the cursor as an opaque token to hide implementation details from clients?
4. What index does MySQL use for the keyset query, and how would you verify it with EXPLAIN?

User Logs Pagination: Implement Efficient Keyset Pagination Over a User Activity Log

Question Details

Full Details

Topics

More from Roblox