955 Queries for a search? Wow, can I get some query help?
Posted: Wed Dec 05, 2007 1:47 pm
I am working on a search that goes through my database and grabs the results (duh). However, the way I have it setup right now, it is taking 955 queries on the DB to generate the results for a single search! And that is just using 2 tables, wow.
Here is the code:
So, as you can see (perhaps) the 'links' table contains links to files as well as what ID in the codes table the links belong to, and the 'codes' table contains the title for the link compilations. What I am trying to do is find links (in the links table) that match the search term, then grab the title for that link from the codes table. Is there a better way to do it than how I am doing it, because its insanely bloated right now (1 query per found result).
Example:
user searches for bob, the links table is searched for "bob" (+1 query), if a result is found the codeid of that link is matched up with the id of the title in the codes table (+1 query).
However there may be 50 to 100+ links associated with a single codeid that links to a id in the codes table, so for each found link it is also re-querying the codes table to get the same information.
Any way to slim down the queries so it doesn't take 900 queries to display 900 search results?
Here is the code:
Code: Select all
function searchLinks($terms)
{
$result = mysql_query("SELECT codeid, link FROM links WHERE link LIKE '%$terms%' ORDER BY noinset Desc")
or die(mysql_error());
$_SESSION['totalqueries']++;
$num_rows = mysql_num_rows($result);
$_SESSION['results'] += $num_rows;
for($i = 0; $i < $num_rows; $i++)
{
$row = mysql_fetch_array($result);
$id = $row['codeid'];
$result2 = mysql_query("SELECT title, getcode FROM codes WHERE id='$id'")
or die(mysql_error());
$row2 = mysql_fetch_array($result2);
$_SESSION['totalqueries']++;
$link = str_replace($terms, "<b>" . $terms . "</b>", $row['link']);
echo "<tr class=\"searchresultsresults\"><td colspan=\"3\" width=\"50%\"><a href=\"index.php?page=get&code=" . $row2['getcode'] . "\">$link</a></td></tr>";
}
if($num_rows == "0")
{
echo "<tr><td colspan=\"3\"><div align=\"center\">No results found</div></td></tr>";
}
} // end searchLinksExample:
user searches for bob, the links table is searched for "bob" (+1 query), if a result is found the codeid of that link is matched up with the id of the title in the codes table (+1 query).
However there may be 50 to 100+ links associated with a single codeid that links to a id in the codes table, so for each found link it is also re-querying the codes table to get the same information.
Any way to slim down the queries so it doesn't take 900 queries to display 900 search results?