bkeane
06-09-2005, 03:17 AM
Thanks to all who have worked on building this great piece of software! :)
I've got a quick suggestion. I don't know if this issue has been addressed already, but ...
Recently I had a fairly large ammount of trafic to my pixelpost site. Much of this traffic was referred from other sites, which caused my referral list (index.php/?x=ref) to become quite large. As the length of my ref list approached 100+ unique referrers (each with hundred of hits), page generation for my whole pp site slowed to a crawl. Each call to index.php was taking 5 to 10 seconds to complete.
The code segment below is what I found to be the cause of the problem (sorry it's so large, but I figgured it would be best to throw in the whole block for context...).
{From index.php}
if($_GET['x'] == "referer") {
$referer_print = "<ul>";
// only count referers from the last seven days
gmdate("Y-m-d H:i:s",time()+(3600 * $cfgrow['timezone'])); // current date+time
$from_date = mktime(0,0,0,gmdate("m",time()+(3600 * $cfgrow['timezone'])) ,gmdate("d",time()+(3600 * $cfgrow['timezone'])) -7,gmdate("Y",time()+(3600 * $cfgrow['timezone'])) );
$from_date = strftime("%Y-%m-%d", $from_date);
$from_date = "$from_date 00:00:00";
$referer = "";
$query = mysql_query("select distinct referer from ".$pixelpost_db_prefix."visitors where (referer!='') AND (datetime>'$from_date')");
while(list($nreferer) = mysql_fetch_row($query)) {
$nreferer = htmlentities($nreferer);
$referer .= "!".$nreferer;
}
$referer = split("!",$referer);
$ref_biglist = "";
foreach($referer as $value) {
if($value != "") {
$row = sql_array("select count(*) as count from ".$pixelpost_db_prefix."visitors where (referer='$value') AND (datetime>'$from_date')");
$refnumb = $row['count'];
$ref_biglist .= "$refnumb@$value!";
}
}
$ref_biglist = split("!",$ref_biglist);
rsort($ref_biglist,SORT_NUMERIC);
foreach($ref_biglist as $value) {
list($numb,$referer) = explode("@",$value);
if($numb > "0") {
if($numb < "10") { $numb = "0$numb"; }
$referername = $referer;
$length = strlen($referername);
if($length > 50) { $referername = substr($referername,0,50); $referername = "$referername..."; }
$referer_print .= "<li><a href='$referer' rel='nofollow'>$numb &nbsp;&nbsp;&nbsp; $referername</a></li>";
}
}
$referer_print .= "</ul>";
$tpl = str_replace("<REFERERS>",$referer_print,$tpl);
} // end refererlog
Below is the code that solves the problem by reducing the number of SQL calls from #of unique referers + 1 calls to just 1 call. This code also saves a bunch of (seemingly?) unneccessary PHP execution time that was involved in building $ref_biglist. As far as I can tell, all original functionality is intact. Please let me know if I'm overlooking something :)!
{Above block should be replaced with this}
if($_GET['x'] == "referer") {
$referer_print = "<ul>";
// only count referers from the last seven days
gmdate("Y-m-d H:i:s",time()+(3600 * $cfgrow['timezone'])); // current date+time
$from_date = mktime(0,0,0,gmdate("m",time()+(3600 * $cfgrow['timezone'])) ,gmdate("d",time()+(3600 * $cfgrow['timezone'])) -7,gmdate("Y",time()+(3600 * $cfgrow['timezone'])) );
$from_date = strftime("%Y-%m-%d", $from_date);
$from_date = "$from_date 00:00:00";
$referer = "";
$query = mysql_query("select referer, count(referer) as hits from ".$pixelpost_db_prefix."visitors where (referer!='') AND (datetime>'$from_date') GROUP BY referer ORDER BY hits desc");
while($myrow = mysql_fetch_array($query)){
$referername = htmlentities($myrow['referer']);
if($myrow['hits'] < 10)
$numb = "0".$myrow['hits'];
else
$numb = $myrow['hits'];
if(strlen($myrow['referer']) > 50)
$referer = htmlentities(substr($myrow['referer']));
else
$referer = htmlentities($myrow['referer']);
$referer_print .= "<li><a href='$referer' rel='nofollow'>$numb &nbsp;&nbsp;&nbsp; $referername</a></li>";
}
$referer_print .= "</ul>";
$tpl = str_replace("<REFERERS>",$referer_print,$tpl);
} // end refererlog
-Brian
I've got a quick suggestion. I don't know if this issue has been addressed already, but ...
Recently I had a fairly large ammount of trafic to my pixelpost site. Much of this traffic was referred from other sites, which caused my referral list (index.php/?x=ref) to become quite large. As the length of my ref list approached 100+ unique referrers (each with hundred of hits), page generation for my whole pp site slowed to a crawl. Each call to index.php was taking 5 to 10 seconds to complete.
The code segment below is what I found to be the cause of the problem (sorry it's so large, but I figgured it would be best to throw in the whole block for context...).
{From index.php}
if($_GET['x'] == "referer") {
$referer_print = "<ul>";
// only count referers from the last seven days
gmdate("Y-m-d H:i:s",time()+(3600 * $cfgrow['timezone'])); // current date+time
$from_date = mktime(0,0,0,gmdate("m",time()+(3600 * $cfgrow['timezone'])) ,gmdate("d",time()+(3600 * $cfgrow['timezone'])) -7,gmdate("Y",time()+(3600 * $cfgrow['timezone'])) );
$from_date = strftime("%Y-%m-%d", $from_date);
$from_date = "$from_date 00:00:00";
$referer = "";
$query = mysql_query("select distinct referer from ".$pixelpost_db_prefix."visitors where (referer!='') AND (datetime>'$from_date')");
while(list($nreferer) = mysql_fetch_row($query)) {
$nreferer = htmlentities($nreferer);
$referer .= "!".$nreferer;
}
$referer = split("!",$referer);
$ref_biglist = "";
foreach($referer as $value) {
if($value != "") {
$row = sql_array("select count(*) as count from ".$pixelpost_db_prefix."visitors where (referer='$value') AND (datetime>'$from_date')");
$refnumb = $row['count'];
$ref_biglist .= "$refnumb@$value!";
}
}
$ref_biglist = split("!",$ref_biglist);
rsort($ref_biglist,SORT_NUMERIC);
foreach($ref_biglist as $value) {
list($numb,$referer) = explode("@",$value);
if($numb > "0") {
if($numb < "10") { $numb = "0$numb"; }
$referername = $referer;
$length = strlen($referername);
if($length > 50) { $referername = substr($referername,0,50); $referername = "$referername..."; }
$referer_print .= "<li><a href='$referer' rel='nofollow'>$numb &nbsp;&nbsp;&nbsp; $referername</a></li>";
}
}
$referer_print .= "</ul>";
$tpl = str_replace("<REFERERS>",$referer_print,$tpl);
} // end refererlog
Below is the code that solves the problem by reducing the number of SQL calls from #of unique referers + 1 calls to just 1 call. This code also saves a bunch of (seemingly?) unneccessary PHP execution time that was involved in building $ref_biglist. As far as I can tell, all original functionality is intact. Please let me know if I'm overlooking something :)!
{Above block should be replaced with this}
if($_GET['x'] == "referer") {
$referer_print = "<ul>";
// only count referers from the last seven days
gmdate("Y-m-d H:i:s",time()+(3600 * $cfgrow['timezone'])); // current date+time
$from_date = mktime(0,0,0,gmdate("m",time()+(3600 * $cfgrow['timezone'])) ,gmdate("d",time()+(3600 * $cfgrow['timezone'])) -7,gmdate("Y",time()+(3600 * $cfgrow['timezone'])) );
$from_date = strftime("%Y-%m-%d", $from_date);
$from_date = "$from_date 00:00:00";
$referer = "";
$query = mysql_query("select referer, count(referer) as hits from ".$pixelpost_db_prefix."visitors where (referer!='') AND (datetime>'$from_date') GROUP BY referer ORDER BY hits desc");
while($myrow = mysql_fetch_array($query)){
$referername = htmlentities($myrow['referer']);
if($myrow['hits'] < 10)
$numb = "0".$myrow['hits'];
else
$numb = $myrow['hits'];
if(strlen($myrow['referer']) > 50)
$referer = htmlentities(substr($myrow['referer']));
else
$referer = htmlentities($myrow['referer']);
$referer_print .= "<li><a href='$referer' rel='nofollow'>$numb &nbsp;&nbsp;&nbsp; $referername</a></li>";
}
$referer_print .= "</ul>";
$tpl = str_replace("<REFERERS>",$referer_print,$tpl);
} // end refererlog
-Brian