Performance problems with big referrer list

[bkeane]

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     $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     $referername</a></li>";
	}
	$referer_print .= "</ul>";
    $tpl = str_replace("<REFERERS>",$referer_print,$tpl);
    } // end refererlog

-Brian

Brian,

that's a very good suggestion! Thank you!