FRD-003
A sliding-window rate limiter in MySQL
No Redis, no extension, no cron. One table and one function that caps any action per key within any window, and fails open so an outage never locks the site.
Shared hosting rarely gives you Redis, and the file-based limiters people write tend to break under concurrency. A single table with an index on key and timestamp handles the volumes a faucet sees without any of that.
The pattern is: record every attempt, count the attempts inside the window, refuse if the count exceeds the cap. Old rows get pruned in the same call so nothing needs a cron.
It fails open on a database error, which is the right choice here — a limiter is a guard rail, not the lock on the door.
/*
CREATE TABLE rate_events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
rkey VARCHAR(120) NOT NULL,
ts DATETIME NOT NULL,
KEY ix_rate (rkey, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/
function rate_hit(PDO $pdo, string $key, int $limit, int $windowSeconds): bool
{
try {
// Prune this key's expired rows, cheaply, on the way through.
$del = $pdo->prepare('DELETE FROM rate_events WHERE rkey = ? AND ts < DATE_SUB(NOW(), INTERVAL ? SECOND)');
$del->execute([$key, $windowSeconds]);
$cnt = $pdo->prepare('SELECT COUNT(*) FROM rate_events WHERE rkey = ? AND ts >= DATE_SUB(NOW(), INTERVAL ? SECOND)');
$cnt->execute([$key, $windowSeconds]);
if ((int) $cnt->fetchColumn() >= $limit) {
return false; // over the cap
}
$ins = $pdo->prepare('INSERT INTO rate_events (rkey, ts) VALUES (?, NOW())');
$ins->execute([$key]);
return true;
} catch (PDOException $e) {
return true; // fail open
}
}Using it
Namespace the key by action and by subject:
rate_hit($pdo, 'login:' . client_ip(), 15, 900)
rate_hit($pdo, 'claim:' . $userId, 1, 3600)
Limit by account and by IP separately. One attacker with many accounts and one account attacked from many IPs are different problems, and a single key catches only one of them.
What bites people
Do not use this as the claim cooldown. A cooldown is a rule the user is entitled to see and to have enforced exactly; a rate limit is a blunt cap. Keep them separate.
The table grows fast on a busy site. The inline prune only clears the key being hit, so add a periodic sweep for keys that stopped being used.
This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.