I need help to optimize my MySQL queries, it takes forever to load

[tjc19999]

I have a script to write out the number of hits each of my websites got each day of the week since I don't trust Google Analytics spying on my websites. The problem is that I have to query each day individually and count how many instances there are with that domain. Since I setup my database give me the time, location, demographics, etc. on each line I'm not sure how to optimize this further. Help would be greatly appreciated.

 

echo '<table><tr><td>Domain</td>';

foreach($timearray as $t){
echo '<td>'.$t.'</td>';
}
echo '</tr>';
$i = 0;
foreach($urlarray as $u){
$today = date("Y-m-d");
$yesterday = date("Y-m-d", mktime(0,0,0,date("m")  , date("d")-1, date("Y")));
$lastmonth  = date("Y-m-d", mktime(0,0,0,date("m")-1  , date("d")-1, date("Y")));
$twoweeks  = date("Y-m-d", mktime(0,0,0,date("m")  , date("d")-15, date("Y")));
$result3 = mysql_query("SELECT refer,Count(*) as count FROM `approved` WHERE time>'$lastmonth 23:59:59' AND time<'$today' AND refer LIKE '%$u%'");
$row3 = mysql_fetch_array($result3);
$result4 = mysql_query("SELECT refer,Count(*) as count FROM `approved` WHERE time>'$twoweeks 23:59:59' AND time<'$today' AND refer LIKE '%$u%'");
$row4 = mysql_fetch_array($result4);
if($row3['count']==0){echo "<tr style='background:red'>";}elseif($row4['count']>0){echo "<tr style='background:green'>";}elseif($i % 2){echo "<tr style='background:#CCC'>";}else{echo "<tr style='background:#FFF'>";}
echo '<td>'.$u.' '.$row3['count'].' '.$row4['count'].'</td>';
foreach($timearray as $t){
	$result = '';
	$result = mysql_query("SELECT COUNT(*) AS count FROM `approved` WHERE time LIKE '$t%' AND refer LIKE '%$u%'");
	while($row = mysql_fetch_array($result)){
		echo '<td>'.$row['count'].'</td>';
	}
}
echo '</tr>';
$i++;
}

echo '</tr></table>';

[PFMaBiSmAd]

It would probably help if you pinned down some of the unknowns.

 

What values are in the $timearray? I cannot imagine a generic value that using LIKE '$t% would uniquely identify different days of the week.

 

Are the `refer` column/values just the domain name or does it also contain variations of the domainname/path/file? Do you need to use LIKE '%$u%' to match the values or for any one domain are they actually identical strings?

 

In general, you would execute one query that gets the data you want in the order that you want it. For the case of getting counts for various time periods, you would use GROUP BY to consolidate the data in the groups you want. I am thinking you would GROUP BY refer, DATE(time) to get the count(*) for each day within each refer.

 

Also in general, you would not execute a query inside of a loop.

[PFMaBiSmAd]

What values are in the $timearray? I cannot imagine a generic value that using LIKE '$t% would uniquely identify different days of the week.

 

I just realized, after going through what the code is trying to produce, that you probably have specific values in the $timearray. It is a list of specific YYYY-MM-DD dates?

[tjc19999]

PFMaBiSmAd,

 

You are correct to assume that the $timearray contains elements in the form of YYYY-MM-DD format. I wanted to make sure that I only display data within a certain time range and it is also what I use to determine the select statement for each URL for each day. As you can assume the $urlarray contains elements like google.com, yahoo.com etc.

[PFMaBiSmAd]

Based on the assumptions, the following should be in the ballpark of how you could do this with a minimum of queries, no queries inside of a loop (actual queries untested of course) -

 

<?php
$today = date("Y-m-d");
$lastmonth  = date("Y-m-d", mktime(0,0,0,date("m")-1  , date("d"), date("Y")));
$twoweeks  = date("Y-m-d", mktime(0,0,0,date("m")  , date("d")-14, date("Y")));

$urlarray = array();	
// get a list of all urls (I'm guessing your $urlarray code is similar to this.)
$query = "SELECT DISTINCT refer FROM `approved` ORDER BY refer"; // you may want to limit this to some past date (a few months ago) so that you only get current urls
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
	$urlarray[] = $row['refer'];
}

$counts = array();
// get the last month counts
$query = "SELECT refer,Count(*) as count FROM `approved` WHERE DATE(time) >= '$lastmonth' GROUP BY refer";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
	$counts[$row['refer']]['lastmonth'] = $row['count'];
}
// get the last twoweek counts
$query = "SELECT refer,Count(*) as count FROM `approved` WHERE DATE(time) >= '$twoweeks' GROUP BY refer";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
	$counts[$row['refer']]['last2weeks'] = $row['count'];
}

$data = array();
// get the data
$query = "SELECT refer,COUNT(*) AS count,DATE(time) as date FROM `approved` WHERE DATE(time) >= '$lastmonth' GROUP BY refer, DATE(time)";
$result = mysql_query($query);	
while($row = mysql_fetch_assoc($result)){
	$data[$row['refer']][$row['date']] = $row['count'];
}

// build a list of dates (used for headings and referencing data)
$dates = array();
$curdate = $lastmonth; // starting value
while($curdate <= $today){
$dates[] = $curdate;
$curdate = date('Y-m-d',strtotime("$curdate + 1 day"));
}

// output table and heading row
echo '<table><tr><th>Domain</th><th>Last<br />Mo</th><th>Last<br />2Wk</th>';
foreach($dates as $date){
echo "<th>". substr($date,0,4) . "<br />" . substr($date,5,5)."</th>"; // YYYY over MM-DD
}
echo "</tr>\n";

$i = 0;
// loop over all the urls
foreach($urlarray as $url){
if(!isset($counts[$url]['lastmonth'])){
	// no records for this url during the range of dates
	$counts[$url]['lastmonth'] = 0;
}
if(!isset($counts[$url]['last2weeks'])){
	// no records for this url during the range of dates
	$counts[$url]['last2weeks'] = 0;
}
if($counts[$url]['lastmonth']==0){
	echo "<tr style='background:red'>"; // lastmonth count == 0
}elseif($counts[$url]['last2weeks']>0){
	echo "<tr style='background:green'>"; // lastmonth != 0 and twoweek count > 0
}elseif($i % 2){
	echo "<tr style='background:#CCC'>"; // lastmonth != 0 and twoweek count == 0, alternate bg color
}else{
	echo "<tr style='background:#FFF'>"; // lastmonth != 0 and twoweek count == 0, alternate bg color
}
echo "<td>$url</td><td>".$counts[$url]['lastmonth']."</td><td>".$counts[$url]['last2weeks']."</td>";
// loop over each day
foreach($dates as $day){
	if(isset($data[$url][$day])){
		echo "<td>".$data[$url][$day]."</td>";
	} else {
		echo '<td>0</td>'; // no records for this url for this day
	}
}
echo "</tr>\n";
$i++;
}
echo '</table>';
?>

 

 

 

 

[tjc19999]

Ah a multi-level array. Great idea, thanks for the help!