Count’em

I wanted to setup a counter for a friends web site, where all the logic is done in the SQL – the reason is that MySQL offers atomic updates (unlike some [read: most] RDBMS which I won’t name here), so I don’t need to lock anything- If I can do all the logic in one statement I get multi-thread safey for free :-).

My initial setup was simply

INSRT INTO counters (name, ip)
VALUES ('counterid','ipaddress')
ON DUPLICATE UPDATE counter = counter + 1

Where the table would be something like (name varchar, ip varchar, counter default 1).
The problem is that if I query SUM(counter) GROUP BY name then I count every hit (reload). OTOH if I query COUNT(counter) GROUP BY name then I count every user once (barring dynamic ips and stuff) regardless of how long it was between reloads.

So I came up with this baby:

CREATE TABLE counters (
id int(10) unsigned NOT NULL auto_increment,
name varchar(50) NOT NULL,
ip varchar(15) NOT NULL,
counter bigint(20) default 1,
updatetime timestamp,
PRIMARY KEY (`id`),
UNIQUE KEY `name_ip` (`name`,`ip`)
)

And I insert like this:

INSERT INTO counters (name, ip)
VALUES ('counter name', 'remote ip address')
ON DUPLICATE KEY UPDATE
counter = IF(
DATE_ADD(updatetime, INTERVAL 21700 SECOND) < NOW(), counter+1, counter)

where 21700 is the preset session length (in the actuall code its of course configurable) - if an IP didn't hit the counter for that long, it will count as a new visit - otherwise it will not count as a new visit, but as we are really updating counter = counter, then it will update the time stamp and “keep the session alive’.
the basic query for a page will be

SELECT SUM(counter) as counter
FROM counters
WHERE name = 'counter name'
GROUP BY name

And I can get a list of counters (for some kind of summary display) using this:

SELECT
name,
SUM(counter) AS total,
MAX(updatetime) AS 'last hit'
FROM counters
GROUP BY name

The only down side is, of course, the fact that the owner also counts as a hit, and most counter users prefer not to count themselves. Its especially important in low-traffic web sites where a couple of visits by the admin (to check up on comments for example) can inflate the count by a meaningful amount.
I can probably check if the user is logged in and has author premissions, and in which case not to run the counter update.

Leave a Reply