slow query...need persistant connection?

sgtpepper

Hello,

I am trying to learn PHP and mysql. So far, it has been pretty easy to pick up....thanks to the help I get from this forum. I am having some problems with my query speed.

I have a search form that places variables in the below script. It seems to take quite a while to get results.

I have heard that it is because I need to use a 'persistent connection'

Can someone help me with this? I dont have the foggiest where to start. Also can you perhaps offer some other suggestions to speed up this very slooooow process?

<?
if(!isset($start)) $start = 0;
$username="XXX";
$password="XXX";
$database="XXX";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT town, list_price, bedrooms, baths, rooms, Id ,IF (lister_office_name like 'Century 21 Highview Realty', 1, 0)
AS featured
FROM residential
WHERE town like '%$selecttown'
AND list_price like '%$selectprice'
AND bedrooms like '%$bedroomsel'
AND baths like '%$bathselect'
ORDER BY featured
DESC LIMIT " . $start . ", 10";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);
?>

<?
$query = "SELECT count(*) as count FROM residential WHERE town like '%$selecttown' AND list_price like '%$selectprice' AND bedrooms like '%$bedroomsel' AND baths like '%$bathselect'";

$result2 = mysql_query($query);
$row = mysql_fetch_array($result2);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\">Previous 10 Listings</a>               \n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">Next 10 Listings</a>\n";
?>

<?
if(mysql_numrows($result) == '0') {
echo "Sorry, no results were found...";
} else {
// display results here
}
?>

<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
?>

</td>
<td width="12%" align="center" bgcolor="#F3F3E0"><a href="test2.php?Id=<? echo $row['Id']; ?>"><b><? echo $row['Id']; ?></b></a>

</td>
<td width="12%" align="center" bgcolor="#F3F3E0"><? echo $row['town']; ?></td>
<td width="12%" align="center" bgcolor="#F3F3E0"><? echo $row['rooms']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['bedrooms']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['baths']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0"><? echo $row['list_price']; ?></td>
<td width="13%" align="center" bgcolor="#F3F3E0">

<a href="test2.php?Id=<? echo $row['Id']; ?>">Click to view more</a>

</td>

</tr>
</table>
<?
}
?>
<?
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\">Previous 10 Listings</a>               \n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">Next 10 Listings</a><BR>\n";
?>

<?php
include("footer.html");
?>

BeastRider

1) Like is not the fastest way to make matches. If there are any fields that you can do an = on, it will avoid having to do substring searches.

2) Key the fields that you are using as the limiting criteria in your search statement. Keyed searches are almost always faster.

superwormy

And no, persistant connections will not help speed up your queries noticeably.

What they do help with is lessening the time it takes to get a connection to the database ( you eliminate the overhead of opening a new one everytime ) , but that only really matters with a site thats under moderate or heavy load.

Sxooter

explain is your friend.

Make your application write the query to the web browser for you, then but and paste it into the mysql monitor and 'splain it:

explain select * from ...

the output from explain can help you identify the slow parts of your query.