mysql rows

motowater

I'm having a speed problem with my mysql rows. I have a database of all the users that have visited my sites and it is close to 300,000 rows. I used to be able to check every day what users have come to my site, but now that my user base has grown, it literally takes me over 25 min just to finish displaying the users for one day.

while($row = mysql_fetch_array($query)){
					echo("<tr>".$row['row1']."</tr>");
				}

That isnt the exact code, but that is base for the row display. I know it is possible to make it faster because phpmyadmin displays it within seconds. I've searched all over google to find another way to display mysql rows faster but I have no idea? Can anyone tell me how to make this significantly faster?

NogDog

I don't see anything in that small snippet that should be a problem (other than if you're going to reference the fields by name, you might as well use [man]mysql_fetch_assoc/man, but that should have negligible effect on the speed, if any). My guess is the problem is elsewhere in the code and/or the query. For the latter, proper indexing of the table(s) in question may help, too.

Kudose

Post your query code. This is most likely the problem.

You can add an index on your WHERE column.
If you've deleted rows, you can run "optimize table <tablename>" from phpMyAdmin

bradgrafelman

You might also consider using a program such as PuTTY (or even M$ Telnet, I suppose) to request the data; perhaps it's a browser issue (e.g. your browser wants to murder you for making it parse/draw such a huge table).

NogDog

I did just notice one thing: where are the <td></td> tags?

motowater

ok I guess I'll just post the whole script, although some of it may not make sense. I know their are better ways to do some of the things in it, but my main concern is to make this faster.

<p><br />
<a name="top"
	href=".<?
	echo ($_SERVER['PHP_SELF']);
	if(strlen($_SERVER['QUERY_STRING']) > 1)
		echo ("?" . $_SERVER['QUERY_STRING']);
	?>#link">Stats</a>
| <a href="<?
echo ($_SERVER['PHP_SELF']);
?>?parse=0">Date</a> | <a
	href="<?
	echo ($_SERVER['PHP_SELF']);
	?>">Main</a>


  <?php
		set_time_limit(0);
		error_reporting(1);
		$startTime = microtime();


	if(stristr($_SERVER['HTTP_HOST'], "www.")){
		$arr = explode(".", $_SERVER['HTTP_HOST']);
		$forward = $arr[1] . "." . $arr[2];
		header('Location: http://' . $forward . '/guestsinfo.php');
	}
	//conect to mysql
	$sqluser = "user";
	$sqlpass = "pass";
	$db = "pageviews";
	$connect = mysql_connect("localhost", $sqluser, $sqlpass);
	mysql_select_db($db, $connect);

	$query = mysql_query("SELECT * FROM `tbl_users`") or die(mysql_error());

	if(isset($_GET['parse'])){
		$parse = $_GET['parse'];
		if($parse == 0){ //cfg page



			echo ('<style type="text/css">
A:link {text-decoration: none}
A:visited {text-decoration: none}
A:active {text-decoration: none}
A:hover {text-decoration: underline; color: red;}
</style>
<form id="timeCfg" name="timeCfg" method="get" action="">
  <input name="parse" type="hidden" id="parse" value="1" />
  <table width="247" border="0" cellpadding="1" cellspacing="1">
    <tr>
      <td width="109">Month</td>
      <td width="60">Day &nbsp;</td>
      <td>Hour &nbsp;</td>
      <td>Minutes &nbsp;</td>
      <td width="43">Year &nbsp;</td>

</tr>
<tr>
  <td><select name="month" id="month">
      <option value="' . date(m) . '" >' . date(F) . ' - ' . date(m) . '</option>
      <option value="01" >January - 01</option>
      <option value="02" >February - 02</option>
      <option value="03" >March - 03</option>
      <option value="04" >April - 04</option>
      <option value="05" >May - 05</option>
      <option value="06" >June - 06</option>
      <option value="07" >July - 07</option>
      <option value="08" >August - 08</option>
      <option value="09" >September - 09</option>
      <option value="10" >October - 10</option>
      <option value="11" >November - 11</option>
      <option value="12" >December - 12</option>
    </select>
    &nbsp;</td>
  <td><select name="day" id="day">
      <option value="' . date(d) . '" >' . date(d) . '</option>
      <option value="01" >1</option>
      <option value="02" >2</option>
      <option value="03" >3</option>
      <option value="04" >4</option>
      <option value="05" >5</option>
      <option value="06" >6</option>
      <option value="07" >7</option>
      <option value="08" >8</option>
      <option value="09" >9</option>
      <option value="10" >10</option>
      <option value="11" >11</option>
      <option value="12" >12</option>
      <option value="13" >13</option>
      <option value="14" >14</option>
      <option value="15" >15</option>
      <option value="16" >16</option>
      <option value="17" >17</option>
      <option value="18" >18</option>
      <option value="19" >19</option>
      <option value="20" >20</option>
      <option value="21" >21</option>
      <option value="22" >22</option>
      <option value="23" >23</option>
      <option value="24" >24</option>
      <option value="25" >25</option>
      <option value="26" >26</option>
      <option value="27" >27</option>
      <option value="28" >28</option>
      <option value="29" >29</option>
      <option value="30" >30</option>
      <option value="31" >31</option>
    </select>
    &nbsp;</td>
    <td><input type="text" class="bginput" name="hour" value="00" size="2" maxlength="2" id="hour" /></td>
    <td><input type="text" class="bginput" name="minute" value="01" size="2" maxlength="2" id="minute" /></td>
  <td><input type="text" class="bginput" name="year" value="' . date(Y) . '" size="4" maxlength="4" id="year" /></td>
</tr>
  </table>
  <br />
  <br />
  <table width="247" border="0" cellpadding="1" cellspacing="1">
          <tr>
            <td width="109">Month &nbsp;</td>
            <td width="60">Day &nbsp;</td>
            <td>Hour &nbsp;</td>
            <td>Minutes &nbsp;</td>
            <td width="43">Year &nbsp;</td>
          </tr>
          <tr>
            <td><select name="month2" id="month2">
          		<option value="' . date(m) . '" >' . date(F) . ' - ' . date(m) . '</option>
                <option value="01" >January - 01</option>
                <option value="02" >February - 02</option>
                <option value="03" >March - 03</option>
                <option value="04" >April - 04</option>
                <option value="05" >May - 05</option>
                <option value="06" >June - 06</option>
                <option value="07" >July - 07</option>
                <option value="08" >August - 08</option>
                <option value="09" >September - 09</option>
                <option value="10" >October - 10</option>
                <option value="11" >November - 11</option>
                <option value="12" >December - 12</option>
              </select>
              &nbsp;</td>
            <td><select name="day2" id="day2">
                <option value="' . date(d) . '" >' . date(d) . '</option>
                <option value="01" >1</option>
                <option value="02" >2</option>
                <option value="03" >3</option>
                <option value="04" >4</option>
                <option value="05" >5</option>
                <option value="06" >6</option>
                <option value="07" >7</option>
                <option value="08" >8</option>
                <option value="09" >9</option>
                <option value="10" >10</option>
                <option value="11" >11</option>
                <option value="12" >12</option>
                <option value="13" >13</option>
                <option value="14" >14</option>
                <option value="15" >15</option>
                <option value="16" >16</option>
                <option value="17" >17</option>
                <option value="18" >18</option>
                <option value="19" >19</option>
                <option value="20" >20</option>
                <option value="21" >21</option>
                <option value="22" >22</option>
                <option value="23" >23</option>
                <option value="24" >24</option>
                <option value="25" >25</option>
                <option value="26" >26</option>
                <option value="27" >27</option>
                <option value="28" >28</option>
                <option value="29" >29</option>
                <option value="30" >30</option>
                <option value="31" >31</option>
              </select>
              &nbsp;</td>
            <td><input type="text" class="bginput" name="hour2" value="' . (date(H) + 2) . '" size="2" maxlength="2" id="hour2" /></td>
            <td><input type="text" class="bginput" name="minute2" value="' . date(i) . '" size="2" maxlength="2" id="minute2" /></td>
            <td><input type="text" class="bginput" name="year2" value="' . date(Y) . '" size="4" maxlength="4" id="year2" /></td>
          </tr>
        </table>
  <p>&nbsp;Domain: <input type="text" class="bginput" name="dom" value="' . $_SERVER['HTTP_HOST'] . '" id="dom" />
  <br />
  <input type="checkbox" name="norows" id="norows" value="1" /> No Rows
  </p>
  <input type="submit" name="Submit" value="Submit" />
</form><br /><br /><br />');
				$siteList[3000];
				$useNum = 0;
				while($row = mysql_fetch_array($query)){
					if(! in_array($row['domain'], $siteList)){
						$siteList[$useNum] = $row['domain'];
						$useNum ++;
					}
				}
				natcasesort($siteList);
				echo ("<a href=\"#\" onclick=\"document.timeCfg.dom.value='*'\"> * </a><br />");
				foreach($siteList as $tmp){
					echo ("<a href=\"#\" onclick=\"document.timeCfg.dom.value='" . $tmp . "'\">" . $tmp . "</a><br />");
				}

		}

	}

	echo "<br><br><br><br><table border='1' align='center'>";
	echo "<tr>";
	echo "<th>ID</th><th>Domain</th><th>Cloak</th><th>User Agent</th><th>Ref</th><th>Page</th><th>IP</th><th>Datetime</th>";
	echo "</tr>";
	$total = 0;
	$totalSpiders = 0;
	$totalRedirects = 0;
	$totalBlanks = 0;
	$totalAlarms = 0;

	$ua = 0;
	$IE = 0;
	$FF = 0;
	$Other = 0;

	$siteTotal = 0;
	$spiders = 0;
	$redirects = 0;
	$blanks = 0;
	$alarms = 0;
	$redirectsToday = 0;

	$totalua = 0;
	$totalIE = 0;
	$totalFF = 0;
	$totalOther = 0;

motowater

code contd..

		

	$dom = $_SERVER['HTTP_HOST'];
	$allDoms = false;
	if(strlen($_GET['dom']) > 1)
		$dom = $_GET['dom'];
	if($_GET['dom'] == '*')
		$allDoms = true;
	$intprot = $_SERVER['HTTP_USER_AGENT'];
	while($row = mysql_fetch_array($query)){
		//get all sites stats
		$total ++;
		$used = 0;
		if($row['cloak'] == 1){
			$totalSpiders ++;
			$used = 1;
		}
		if($row['cloak'] == 2){
			$totalRedirects ++;
			$used = 2;
		}
		if($row['cloak'] == 3){
			$totalBlanks ++;
			$used = 3;
		}
		if(stristr($row['useragent'], "Xenu") || $row['ip'] == '71.201.6.81' || $row['ip'] == $intprot){
			if($used == 1)
				$totalSpiders --; else if($used == 2)
				$totalRedirects --; else if($used = 3)
				$totalBlanks --;
			$total --;
		}
		if($used != 1){
			if(stristr($row['useragent'], "MSIE"))
				$totalIE ++; else if(stristr($row['useragent'], "Mozilla") && stristr($row['useragent'], "Gec"))
				$totalFF ++; else
				$totalOther ++;
		}


		if($_REQUEST['parse'] == 1){ // print and calculate stats for parsed page
			if($row['domain'] == $dom || $allDoms){
				$early = mktime($_GET['hour'], $_GET['minute'], 0, $_GET['month'], $_GET['day'], $_GET['year']);
				$late = mktime($_GET['hour2'], $_GET['minute2'], 0, $_GET['month2'], $_GET['day2'], $_GET['year2']);
				if($late >= strtotime($row['datetime']) && $early <= strtotime($row['datetime'])){
					$siteTotal ++;
					$color = "";
					$used = 0;
					if($row['cloak'] == 1){
						$color = " bgcolor=#AEF9B0";
						$spiders ++;
						$used = 1;
					}
					if($row['cloak'] == 2){
						$color = " bgcolor=#EF9E9C";
						$redirects ++;
						$used = 2;
					}
					if($row['cloak'] == 3){
						$color = " bgcolor=#BCDBDC";
						$blanks ++;
						$used = 3;
					}

					if($used != 1){
						if(stristr($row['useragent'], "MSIE"))
							$IE ++; else if(stristr($row['useragent'], "Mozilla") && stristr($row['useragent'], "Gec"))
							$FF ++; else
							$Other ++;
					}

					$check = mysql_query("SELECT * FROM `tbl_users` WHERE ip='$intprot'") or die(mysql_error());
					if(stristr($row['ref'], "cache") || mysql_num_rows($check) > 3 || stristr($row['ref'], "site:")){
						$color = " bgcolor=#FF1A1A";
						$alarms ++;
					}

					if(stristr($row['useragent'], "Xenu") || $row['ip'] == '71.201.6.81' || $row['ip'] == $intprot){
						$color = " bgcolor=#CCCCCC";
						if($used == 1)
							$spiders --; else if($used == 2)
							$redirects --; else if($used = 3)
							$blanks --;
					}

					if($_GET['norows'] != 1){ //no rows displayed
						echo "<tr" . $color . ">";
						echo "<td>", $row['id'], "&nbsp;</td><td>", $row['domain'], "&nbsp;</td><td>", $row['cloak'], "&nbsp;</td><td>", $row['useragent'], "&nbsp;</td><td>", $row['ref'], "&nbsp;</td><td>", $row['page'], "&nbsp;</td><td>", $row['ip'], "&nbsp;</td><td>", $row['datetime'], "&nbsp;</td>";
						echo "</tr>";
					}
				}
			}
		} else if(! isset($_GET['parse']))// print and calculate stats for non parsed page
{


			if($row['domain'] == $dom){
				$siteTotal ++;
				$color = "";
				$used = 0;
				if($row['cloak'] == 1){
					$color = " bgcolor=#AEF9B0";
					$spiders ++;
					$used = 1;
				}
				if($row['cloak'] == 2){
					$color = " bgcolor=#EF9E9C";
					$redirects ++;
					$used = 2;
				}
				if($row['cloak'] == 3){
					$color = " bgcolor=#BCDBDC";
					$blanks ++;
					$used = 3;
				}

				if($used != 1){
					if(stristr($row['useragent'], "MSIE"))
						$IE ++; else if(stristr($row['useragent'], "Mozilla") && stristr($row['useragent'], "Gec"))
						$FF ++; else
						$Other ++;
				}

				$check = mysql_query("SELECT * FROM `tbl_users` WHERE ip='$intprot'") or die(mysql_error());
				if(stristr($row['ref'], "cache") || mysql_num_rows($check) > 3){
					$color = " bgcolor=#FF1A1A";
					$alarms ++;
				}

				if(stristr($row['useragent'], "Xenu") || $row['ip'] == '71.201.6.81' || $row['ip'] == $intprot){
					$color = " bgcolor=#CCCCCC";
					if($used == 1)
						$spiders --; else if($used == 2)
						$redirects --; else if($used = 3)
						$blanks --;
				}
				if($_GET['norows'] != 1){ //no rows displayed
					echo "<tr" . $color . ">";
					echo "<td>", $row['id'], "&nbsp;</td><td>", $row['domain'], "&nbsp;</td><td>", $row['cloak'], "&nbsp;</td><td>", $row['useragent'], "&nbsp;</td><td>", $row['ref'], "&nbsp;</td><td>", $row['page'], "&nbsp;</td><td>", $row['ip'], "&nbsp;</td><td>", $row['datetime'], "&nbsp;</td>";
					echo "</tr>";
				}
			}
		}
	}

	$endTime = microtime();

	echo "</table><br /><br />";

	echo "<span align=center>";

	echo ('<table width="300" border="1" align="center">
  <tr>
    <td>
	<b><u>This Site</u></b><br /><br />
	Site total: <b>' . $siteTotal . '</b><br>
	Spider visits: <b>' . $spiders . '</b><br>
	Redirect visits: <b>' . $redirects . '</b><br>
	404 visits: <b>' . $blanks . '</b><br>
	Alarms: <b>' . $alarms . '</b><br>
	<br>
	MSIE Users: <b>' . $IE . ' (' . round(($IE / ($IE + $FF + $Other)) * 100, 2) . '%)</b><br>
	Mozilla Users: <b>' . $FF . ' (' . round(($FF / ($IE + $FF + $Other)) * 100, 2) . '%)</b><br>
	Other Users: <b>' . $Other . ' (' . round(($Other / ($IE + $FF + $Other)) * 100, 2) . '%)</b><br>
	</td>
  </tr>
  <tr>
    <td>
	<b><u>All Sites</u></b><br /><br />
	All sites total: <b>' . $total . '</b><br>
	All Spider visits: <b>' . $totalSpiders . '</b><br>
	All Redirect visits: <b>' . $totalRedirects . '</b><br>
	All 404 visits: <b>' . $totalBlanks . '</b><br>
	All Alarms: <b>' . $totalAlarms . '</b><br>
	<br>
	MSIE Users: <b>' . $totalIE . ' (' . round(($totalIE / ($totalIE + $totalFF + $totalOther)) * 100, 2) . '%)</b><br>
	Mozilla Users: <b>' . $totalFF . ' (' . round(($totalFF / ($totalIE + $totalFF + $totalOther)) * 100, 2) . '%)</b><br>
	Other Users: <b>' . $totalOther . ' (' . round(($totalOther / ($totalIE + $totalFF + $totalOther)) * 100, 2) . '%)</b><br>

</td>
  </tr>
  <tr>
    <td>
	<b><u>More</u></b><br /><br />
	Execution Time: <b>' . ($startTime-$endTime) . ' sec</b><br>

</td>
  </tr>
</table>');


	echo "</span><br><br><br><br><br><br>";
	?>
<a name="link"
	href="<?
	echo ($_SERVER['PHP_SELF']);
	if(strlen($_SERVER['QUERY_STRING']) > 1)
		echo ("?" . $_SERVER['QUERY_STRING']);
	?>#top">Top</a>
| <a href="<?
echo ($_SERVER['PHP_SELF']);
?>?parse=0">Date</a> | <a
	href="<?
	echo ($_SERVER['PHP_SELF']);
	?>">Main</a></p>

Kudose

Two (among other) things you can do:

Alter your query to include the column names you want instead of all of them (*)
Add pagination to your results (use a LIMIT clause)

motowater

I end up displaying all the colunms in the table on the web page, so the first suggestion wouldnt really help. As for pagination that is not quite what I'm looking for. On top of that, I've tried calling the script maybe an hour after midnight to display users for the day, where the amount of users displayed is small anyway, and it still took well over 5 minutes. I think it has to do with the massive amount of rows in the database, and I am trying to find a completely different way to print the rows because that seems like the only way to significantly improve call time. Any more ideas?

leatherback

You are very clearly not jsut spitting out a few rows. You are doing a lot of inefficient comparisons on is e.g., a lot of if() statements, where a switch would be much more effective, or even using an array to count would make it much faster.
$Count = array(1=>0, 2=>, 3=>0);
$****[$row['cloak']]++;
Of course things like mktime take a lot of time; why did you not have mysql make the time for you?

Looks to me like you are parsing a log. Unless you truly get that many users on a day, I would consider caching the parsed file, storing the last parsed record and only parsing the new records

Weedpacket

leatherback wrote:
using an array to count would make it much faster.

As well as the date calculations I think a lot of those totals could be performed by the dbms; what's more, only the queries that are needed could be performed (instead of reading the entire table into memory regardless of what it's actually going to be used for). So I agree, there could be some more loading on the database.

Unless you truly get that many users on a day, I would consider caching the parsed file, storing the last parsed record and only parsing the new records

In between those two I'd add another set of db tables for storing the appropriately-calculated stats: keep those current and generate pages from those, and just use the log as a log.

Kudose

You can also try running 'optimize table <tablename>' if you've deleted a lot of rows.