> > 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/en/innodb-next-key-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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
