...

View Full Version : performance



jedimastermopar
11-30-2006, 09:41 PM
Just wondering how long it should take to run this query? The server is fast, dual cpu xeon 2ghz 4 gig of ram fast raid0+1 HD's
Each table has about 6,000 records with the full query pulling in about 10,000 records.
I have the result paginated to 25 results. It takes around 3-5 seconds to display each page.

$query = "SELECT company.CompanyName,
contact.Name,
contact.Email,
branch.Address,
branch.City,
branch.Province,
branch.PostalCode,
branch.Phone,
contact.Cell,
branch.Fax,
company.CompanyID,
branch.BranchID,
contact.ContactID,
credit.Status
FROM soileng.branch branch
LEFT OUTER JOIN soileng.contact contact ON branch.BranchID=contact.ParentBranchID
LEFT OUTER JOIN soileng.company company ON branch.ParentCompanyID=company.CompanyID
LEFT OUTER JOIN soileng.credit credit ON company.CompanyID=credit.CompanyID
WHERE $searched LIKE '%$trimmed%'
ORDER BY $sorting ";

jedimastermopar
11-30-2006, 09:43 PM
when I run the same query through MySQL control center it takes 0.28 sec to retrieve 1000 results.

marek_mar
11-30-2006, 10:50 PM
A miracle?
You could time the query in PHP and see how much does the actual query take and how much does the rest of your PHP code take.
You really should post some code. How is someone supposed to help you having only the query which you think is not the problem?

firepages
12-01-2006, 01:13 AM
I see no LIMIT set.. are you pulling all 10,000 records out and then using PHP to show only 25 ? you should be using `LIMIT $offset,25` or similar ... but as marek notes we are guessing without seeing more code.

jedimastermopar
12-01-2006, 03:16 AM
Sorry here is the code for the page.
Pretty bare bones, search and retrieve page, but it gets the job done, justa bit slowly right now.
I should also mention that I doubt it is network performance or anything like that the end pc is very fast as well as the switched gigabit network. I have a feeling it is like you say,

LIMIT is defined as a variable just before I call the query.




<form name="form" action="contactlookup.php" method="get">
<label>
Look for
<input name="q" type="text"/>
under
<select name="column">
<option value="contact.Name">Contact Name</option>
<option value="contact.Email">Contact Email</option>
<option value="company.CompanyName">Company Name</option>
<option value="branch.Address">Address</option>
<option value="branch.City">City</option>
<option value="branch.Province">Province</option>
<option value="branch.PostalCode">Postal Code</option>
<option value="branch.Phone">Phone</option>
<option value="contact.Cell">Contact Cell</option>
<option value="branch.Fax">Fax</option>
<option value="company.CompanyID">Company ID</option>
<option value="branch.BranchID">Branch ID</option>
<option value="contact.ContactID">Contact ID</option>
<option value="credit.Status">Black List Status</option>
</select>
</label>
and show
<label>
<select name="perpage">
<option value="1">1</option>
<option value="10">10</option>
<option value="25" selected="selected">25</option>
<option value="50">50</option>
<option value="100">100</option>
<option value="200">200</option>
<option value="500">500</option>
<option value="1000">1000</option>
</select>
</label>
results per page and sort by
<select name="sort">
<option value="company.CompanyName" selected="selected">Company Name</option>
<option value="contact.Name" >Contact Name</option>
<option value="contact.Email">Contact Email</option>
<option value="branch.Address">Address</option>
<option value="branch.City">City</option>
<option value="branch.Province">Province</option>
<option value="branch.PostalCode">Postal Code</option>
<option value="branch.Phone">Phone</option>
<option value="contact.Cell">Contact Cell</option>
<option value="branch.Fax">Fax</option>
<option value="company.CompanyID">Company ID</option>
<option value="branch.BranchID">Branch ID</option>
<option value="contact.ContactID">Contact ID</option>
<option value="credit.Status">Black List Status</option>
</select>
<input type="submit" name="Submit" value="Search" />
</form>
<span class="style1">
<?php
$var = @$_GET['q'] ;
$trimmed = trim($var);
$searched = @$_GET['column'] ;
$sorting = @$_GET['sort'] ;
$rowsPerPage = @$_GET['perpage'] ;

@mysql_connect("x.x.x.x","pass","password");
@mysql_select_db("soileng") or die("Can not find database");

$searched . ' column.';

$pageNum = 1;

if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}


$offset = ($pageNum - 1) * $rowsPerPage;

$query = "SELECT company.CompanyName,
contact.Name,
contact.Email,
branch.Address,
branch.City,
branch.Province,
branch.PostalCode,
branch.Phone,
contact.Cell,
branch.Fax,
company.CompanyID,
branch.BranchID,
contact.ContactID,
credit.Status
FROM soileng.branch branch
LEFT OUTER JOIN soileng.contact contact ON branch.BranchID=contact.ParentBranchID
LEFT OUTER JOIN soileng.company company ON branch.ParentCompanyID=company.CompanyID
LEFT OUTER JOIN soileng.credit credit ON company.CompanyID=credit.CompanyID
WHERE $searched LIKE '%$trimmed%'
ORDER BY $sorting ";

$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Please enter a Search String, Leave Search field blank to see all records');

echo '<table border="2" cellspacing="1" cellpadding="1" align="left" >
<tr><td align="left"><b>CompanyName</b></td>
<td align="left"><b>ContactName</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Address</b></td>
<td align="left"><b>City</b></td>
<td align="left"><b>Province</b></td>
<td align="left"><b>PostalCode</b></td>
<td align="left"><b>Phone</b></td>
<td align="left"><b>Cell</b></td>
<td align="left"><b>Fax</b></td>
<td align="left"><b>Comp ID</b></td>
<td align="left"><b>Branch ID</b></td>
<td align="left"><b>Cont ID</b></td>
<td align="left"><b>Status</b></td>
</tr>';

while(list( $CompanyName, $Name, $Email, $Address, $City, $Province, $PostalCode, $Phone, $Cell, $Fax, $CompanyID, $BranchID,$ContactID,$Status) = mysql_fetch_array($result))
{
if ($Status == 'Black') { echo "<tr bgcolor=#FF0000>"; }
elseif ($Status == 'Doubtful') { echo "<tr bgcolor=#FFFF00>"; }
else { echo "<tr bgcolor=#FFFFFF>"; }
echo"<td nowrap>$CompanyName</td>
<td nowrap>$Name</td>
<td nowrap>$Email</td>
<td nowrap>$Address</td>
<td nowrap>$City</td>
<td nowrap>$Province</td>
<td nowrap>$PostalCode</td>
<td nowrap>$Phone</td>
<td nowrap>$Cell</td>
<td nowrap>$Fax</td>
<td nowrap>$CompanyID</td>
<td nowrap>$BranchID</td>
<td nowrap>$ContactID</td>
<td nowrap>$Status</td>
</tr>";
}

echo '<tr>';

$result = mysql_query($query) or die('Error, query failed you sucka');
$numrows = mysql_num_rows($result);

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Prev]</a> ";

$first = " <a href=\"$self?page=1&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] ';
$first = ' [First Page] ';
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage&q=$var&column=$searched&perpage=$rowsPerPage&sort=$sorting\">[Last Page]</a> ";
}
else
{
$next = ' [Next] ';
$last = ' [Last Page] ';
}

echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;
echo '</tr>';
echo '</table>';
echo '<br>';
?>
</span> </td>
</tr>
</table>
</body>

jedimastermopar
12-01-2006, 03:22 AM
How do I setup a timer for the query in php? I have seen that in a few pages before. I have always thought that was really cool.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum