Databases Reference
In-Depth Information
The problem is that this single row is effectively a global “mutex” for any transaction
that updates the counter. It will serialize those transactions. You can get higher con-
currency by keeping more than one row and updating a random row. This requires the
following change to the table:
mysql>
CREATE TABLE hit_counter (
->
slot tinyint unsigned not null primary key,
->
cnt int unsigned not null
->
) ENGINE=InnoDB;
Prepopulate the table by adding 100 rows to it. Now the query can just choose a random
slot and update it:
mysql>
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
To retrieve statistics, just use aggregate queries:
mysql>
SELECT SUM(cnt) FROM hit_counter;
A common requirement is to start new counters every so often (for example, once a
day). If you need to do this, you can change the schema slightly:
mysql>
CREATE TABLE daily_hit_counter (
->
day date not null,
->
slot tinyint unsigned not null,
->
cnt int unsigned not null,
->
primary key(day, slot)
->
) ENGINE=InnoDB;
You don't want to pregenerate rows for this scenario. Instead, you can use
ON DUPLICATE
KEY UPDATE:
mysql>
INSERT INTO daily_hit_counter(day, slot, cnt)
->
VALUES(CURRENT_DATE, RAND() * 100, 1)
->
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
If you want to reduce the number of rows to keep the table smaller, you can write a
periodic job that merges all the results into slot 0 and deletes every other slot:
mysql>
UPDATE daily_hit_counter as c
->
INNER JOIN (
->
SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
->
FROM daily_hit_counter
->
GROUP BY day
->
) AS x USING(day)
->
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
->
c.slot = IF(c.slot = x.mslot, 0, c.slot);
mysql>
DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;
