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.