> Given: MySQL 4.0.12, I need to implement a pageview log with a
> resolution of 1 day.
>
> I propose this table:
>
> CREATE TABLE `pageviews` (
> `id` int(11) NOT NULL auto_increment,
> `date` date NOT NULL default '0000-00-00',
> `url` char(120) NOT NULL default '',
> `views` mediumint(9) NOT NULL default '0',
> PRIMARY KEY (`id`),
> UNIQUE KEY `date` (`date`,`url`),
> KEY `url` (`url`)
>) TYPE=InnoDB;>>>>>>
>
>
> So that an update will look like:
> > UPDATE pageviews SET views=views+1 WHERE date='<DATE>' AND
> url='<ARTIST>'
>
> Of course I need to INSERT the record if one does not match my WHERE.
> This would be easy if I had 4.1 -- "INSERT ... ON DUPLICATE KEY UPDATE",
> I think -- but I do not.

Would the "REPLACE" method work?

David

> > Given: MySQL 4.0.12, I need to implement a pageview log with a
> > resolution of 1 day.
.......
>
> Would the "REPLACE" method work?
>
> David


Hmmm...as I read the docs, the "LOCK IN SHARED MODE" seemed to be the
real key to this.


I created a test script and ran:

$ ab -n100 -c100 localhost/hits.php

Where hits.php looks like:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>
<?php
/**
* Import db connection parameters
*/

require $_SERVER['DOCUMENT_ROOT'] . '/generic/app_global.inc.php';

$err = null;
($date = $_GET['date']) or ($date = date('Y-m-d'));
($url = $_GET['url']) or ($url = $_SERVER['HTTP_REFERER']);

/**
* For testing, get a random date and URL
*/
$dates = array(
'2007-09-11',
'2007-09-12',
'2007-09-13',
);
$urls = array(
'URL A',
'URL B',
'URL C',
);

shuffle($dates);
shuffle($urls);

$date = pos($dates);
$url = pos($urls);

/**
* Connect
*/
$dblink =
mysql_connect($page_options['host_main'],$page_options['host_main_user']
,$page_options['host_main_pass']);
mysql_select_db('articles',$dblink);


/**
* BEGIN TRANSACTION
*/
$rs = mysql_query('START TRANSACTION',$dblink);
$debug = 'Begin Transaction said:"'.mysql_error($dblink).'"';
error_log($debug."\n", 3, '/tmp/errors.log');

// see:
http://dev.mysql.com/doc/refman/4.1/...y-locking.html
$sql = "SELECT views FROM pageviews WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."' LOCK IN SHARE MODE";

/**
* If NO records are returned, we need to INSERT with our first pageview
*/
$rs = mysql_query($sql,$dblink);
if( mysql_num_rows($rs) == 0 ) {
$sql = "INSERT INTO pageviews SET views=1,
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
} else {
$sql = "REPLACE INTO pageviews SET
views=".(intval(mysql_result($rs,0,'views'))+1).",
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
//$sql = "UPDATE pageviews SET views=views+1 WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."'";
}
echo $sql;
$rs = mysql_query($sql,$dblink);

/**
* Barely error-checking...
*/
if ( mysql_affected_rows($dblink) != 1 ) {
$err = mysql_error($dblink);
error_log ($err."\n", 3, '/tmp/errors.log');
}
mysql_query('COMMIT',$dblink);

?>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>

As you can see, I tried *both* the "REPLACE INTO" and "UPDATE" queries
and received *very* strange results. I sum(views) and get roughly 115
views!! I expected 100 or less, but maybe I do NOT understand 'ab'.

So, I added this:
error_log('foo'."\n", 3, '/tmp/errors.log');
exit;

At the top of my script, and ran:
$ ab -n100 -c100 localhost/hits.php

Again, expecting 100 'foo's -- I get roughly 160!

What the hell? I guess I really *don't* understand ab...

Thoughts?

--
Wellington

Fan, Wellington wrote:
> > > Given: MySQL 4.0.12, I need to implement a pageview log with a
>>> resolution of 1 day.

If you want to brute-force it, I think I would go this route:

create table hits (
day date not null primary key,
hitcount int unsigned not null,
);
insert ignore into hits(day, hitcount) values (current_date, 0);
update hits set hitcount = hitcount + 1 where day = current_date;

No transactions. Your application logic can perhaps be smart and avoid
the first query. But the transactional method with locking in share
mode is probably going to have a lot more overhead and lower concurrency
than my suggestion.

Baron