results taking a LONG TIME

[robsgaming]

I have a tracking script. When there are just a few hundred rows in the database, it comes up quick. Now it has around 450,000 rows, and it takes it like 30 seconds to come up. I am displaying 30 rows at a time with previous and next.

What could be causing the results to come up so slow when there are this many rows? In phpMyAdmin, they come up FAST.

Here is the code I'm using:

<? require("nrg_vars.php"); ?>
<? include("nrg_stylesheet.php"); ?>
<?

$timec = date("g:i:s a");

$todaytime = date("m-d-Y");

mysql_select_db("###",$db);

// today unique hits
$unqquery = "SELECT * FROM ref_track where time like '$todaytime%' GROUP BY remoteaddr";
$unqresult = MYSQL_QUERY($unqquery);
$numtoday = mysql_num_rows($unqresult);

// today page views
$pgequery = "SELECT * FROM ref_track where time like '$todaytime%'";
$pgeresult = MYSQL_QUERY($pgequery);
$pagetoday = mysql_num_rows($pgeresult);

// since log unique hits
$unqquerylog = "SELECT * FROM ref_track GROUP BY remoteaddr";
$unqresultlog = MYSQL_QUERY($unqquerylog);
$numunqlog = mysql_num_rows($unqresultlog);


// first entry
$sqlfirst = "SELECT * FROM ref_track LIMIT 0,1";
$resultfirst = mysql_query($sqlfirst);
$myrowfirst = mysql_fetch_array($resultfirst);
$firstentry = $myrowfirst["time"];


if($trackRef)
	{
		$sql_text = " SELECT * FROM ref_track WHERE refcode = '$trackRef' ORDER BY id DESC";
	}
else if($searchEngine)
	{
		$sql_text = "SELECT * FROM ref_track WHERE referer like '%$searchEngine%' ORDER BY id DESC";
	}
	else
	{
		$sql_text = "SELECT * FROM ref_track ORDER BY id desc";
	}



$databasename = "###";
$databaselogin = "###";
$databasepassword = "###";
$toprowcolor = "#fbc42c";
$headercolor = "#fbc42c";
$headerfontcolor = "#000000";
$maintablecolor = "#A1A1A1";
$bottomrowcolor = "#fbc42c";
$url = "";



if($gamename == ""){
                                                           $per_page = "50";

}

mysql_connect("localhost", "###", "###");
if (!$spage) {  

   $spage = 1;  

}  

$prev_page = $spage - 1;  

$next_page = $spage + 1;  

$query = mysql_db_query("###", $sql_text); 
$page_start = ($per_page * $spage) - $per_page;  

$num_rows = mysql_num_rows($query);  

if ($num_rows <= $per_page) {  

   $num_pages = 1;  

} else if (($num_rows % $per_page) == 0) {  

   $num_pages = ($num_rows / $per_page);  

} else {  

   $num_pages = ($num_rows / $per_page) + 1;  

}  

$num_pages = (int) $num_pages;  

if (($spage > $num_pages) || ($spage < 0)) {  

   echo "You have specified an invalid page number";  

}  

$sql_text = $sql_text . " LIMIT $page_start, $per_page";  

$query = mysql_db_query("###", $sql_text);  

echo "

<table border='0' width='100%'>
<tr>
<td width='50%'>
<font face=\"Tahoma\" size=\"3\"><B>Tracking Script</B></font><BR>

<font face=\"Tahoma\" size=\"1\"><B>Current Server Time:</B> $timec</font>
<P>
<form action=\"$PHP_SELF\">
<font size=1 face=tahoma>Sort:</font>&nbsp;<select name=\"searchEngine\">
<option selected>$searchEngine</option>
<option value=\"altavista\">altavista</option>
<option value=\"google\">google</option>
<option value=\"msn\">msn</option>
<option value=\"stationplay\">stationplay</option>
<option value=\"yahoo\">yahoo</option>
</select>&nbsp;<input type=\"submit\" value=\"Go!\">
</form>&nbsp; <a href=\"$PHP_SELF\"><font face=tahoma size=1>or click here to view all</font></a>
<P>
</td>
<td width='50%'>
<font class='textsmall'>
<B>Today's Unique Vistors:</B> $numtoday<BR>
<B>Today's Page Views:</B> $pagetoday<P>
<B>Since $firstentry Unique Visitors:</B> $numunqlog<BR>
<B>Since $firstentry Page Views:</B> $num_rows<BR>
</font>
<P>
</td>
</tr>
</table>

<P>
<table width=100% cellspacing=0 cellpadding=5> 


<tr><td width=5% colspan=4 align=left bgcolor=$bottomrowcolor>";

if ($prev_page)  { 
   echo "<a href=\"$PHP_SELF?spage=$prev_page\"><font face=arial size=2><b><<< Prev 50</b></font></a>"; 
}else{
   echo "&nbsp;";}
echo "<td width=5% align=right bgcolor=$bottomrowcolor>";
if ($spage != $num_pages) {  

   echo "<a href=\"$PHP_SELF?spage=$next_page\"><font face=arial size=2><b>Next 50 >>></font></b></a>" ;
}else{
   echo "&nbsp;";}
echo "</td></tr>



<tr>
      <td width=5% bgcolor=#FFE095 bordercolor=#185470><font face=Arial size=2><b>ID</b></font></td>
      <td width=60% bordercolor=$headercolor bgcolor=#FFE095><font face=Arial size=2><b>REFERER</b></font></td>
      <td width=15% bordercolor=$headercolor bgcolor=#FFE095><font face=Arial size=2><b>REFCODE</b></font></td>
      <td width=10% bordercolor=$headercolor bgcolor=#FFE095><font face=Arial size=2><b>ADDRESS</b></font></td>
      <td width=10% bordercolor=$headercolor bgcolor=#FFE095><font face=Arial size=2><b>TIME</b></font></td>




</tr>";
while ($result = mysql_fetch_array($query)) {  


If ($vario==0) {$bg="#C0C0C0";} else {$bg="#E0E0E0";} $vario++;  if ($vario>1) {$vario=0;}

  echo "<tr>


  <td width=5% bgcolor=$bg bordercolor=$maintablecolor><font face=Arial size=1>$result[0]</font></td>";
if ($result[1] == "Direct Hit") { echo "<td width=60% bordercolor=$maintablecolor bgcolor=$bg><font face=Arial size=1>$result[1]</font></td>"; } else { echo "<td width=60% bordercolor=$maintablecolor bgcolor=$bg><font face=Arial size=1><a href='$result[1]' target='newwin'>$result[1]</a></font></td>"; }
echo "
      <td width=15% bordercolor=$maintablecolor bgcolor=$bg><font face=Arial size=1><a href='$PHP_SELF?trackRef=$result[5]'>$result[5]</a></font></td>
      <td width=10% bordercolor=$maintablecolor bgcolor=$bg><font face=Arial size=1>$result[3]</font></td>
      <td width=10% bordercolor=$maintablecolor bgcolor=$bg><font face=Arial size=1>$result[4]</font></td>
";   

}  

echo "</tr><tr><td width=5% colspan=4 align=left bgcolor=$bottomrowcolor>";

if ($prev_page)  { 
   echo "<a href=\"$PHP_SELF?spage=$prev_page\"><font face=arial size=2><b><<< Prev 50</b></font></a>"; 
}else{
   echo "&nbsp;";}
echo "<td width=5% align=right colspan=3 bgcolor=$bottomrowcolor>";
if ($spage != $num_pages) {  

   echo "<a href=\"$PHP_SELF?spage=$next_page\"><font face=arial size=2><b>Next 50 >>></font></b></a>" ;
}else{
   echo "&nbsp;";}
echo "</td></tr></table>";




?>

[dnszero]

Based on your problem description, it sounds like you should look to your DB
for answers.

Start by running explain on the querries you're using and then add indexes
[sp?] where appropriate. Check out this
link for more info:
http://www.phpbuilder.com/columns/tim20010110.php3

Also, if you run the querries straight from the mysql console, it will tell you
how long it takes each one to execute. You can use this info to determine
which querries are the most trouble prone and start with those first.

Once you have all your querries running as fast as possible, then take a look
through your code to see if there's anything you can optimize there.
Generally the best places to start with are loops. You can use the various time
functions and some print statements to
find out how long each section your interested in takes to execute.

If you're still having trouble, try to narrow the problem down to a specific
section and then post back.

[Weedpacket]

Two obvious improvements to the queries themselves are:

1. Select only the fields you actually need - don't just chuck a * in there and get every single field whether you need it or not.

2. Instead of asking for all the records and then counting them, just ask for the count in the first place - use a query that asks for a COUNT of the
   rows: "SELECT COUNT(*) FROM ... WHERE ...". Only a single record with a single field gets sent back - not who knows how many records with who knows how many fields.

[ksandom]

Looks like you are doing the complete search with every page retrieval (did I just sound like MS Office Assistant?).

One way you could reduce search time is to do the full search and:

1) Stick the results into a temporary table. Include an expiry just in case results are never looked at.

2) Serialize the results and put them into a session variable. I put this as option 2, because the more I think about it, the stupider this idea becomes. I think there's a very definite size limit which you will almost definitley cross in this situation. But I've left this idea in because it might spring another thought somewhere.

Eitherway, the main search is only being done once. Subsequent pages are retrieved from a much smaller table. If you want to get really cleaver, you could look into doing some form of cacheing in this way, but I suspect this would be a lot more effort than it would be worth.

Sorry, not really any answers in this post, but hope it helps a little.

[robsgaming]

thanks for all of the help. I'll see what i can do.