Query optimization

[richie]

Hi,

I am looking for a way to optimize the query below.
Basically, here is what I'm doing:

1. I perform a search
2. I then iterate through the results
3. Within each iteration, I perform more queries.

Unfortunately, the query is slow.
Retrieval of 1,000 records takes about 2 minutes 20 seconds.

Here is the query:

$qry = "select mD.*,mC.category as cat,mT.mType as type from ";
$qry .= "movieDescr mD, mediaCategories mC, mediaType mT where";
$qry .= " mD.status='act' && mD.category=mC.id && mT.id=mD.type ";
$qry .= "&& mD.inWhse='1' order by mD.category desc limit $limit ";
$result = mysql_db_query($db,$qry,$connection);

while($row=mysql_fetch_array($result)) {

	$id = $row['id'];


	/////////////// get total copies
	$getCopies = "select count(id) from movies where id='$id' && active='y' ";
	$r = mysql_db_query($db,$getCopies,$connection);
	$count = mysql_result($r, 0);


	 ///////////// get available copies
	$getCopies = "select count(id) from movies where id='$id' ";
	$getCopies .= "&& inWhse='1' && active='y' ";
	$r = mysql_db_query($db,$getCopies,$connection);
	$availCount = mysql_result($r, 0);


	///////////// get history
	$getHistQry = "select (TO_DAYS(now()) - TO_DAYS(returnDate)) as ";
	$getHistQry .= "numDays from history where login='$value' && ";
	$getHistQry .= "titleId='$id' && seenYet='1' limit 1";
	$histRes = mysql_db_query($db,$getHistQry,$connection);
	$histResRow = mysql_fetch_object($histRes);
	$daysCount=''; $daysCount = $histResRow->numDays;


	///////////// verify availability?
	$getLocQry = "select count(id) from movies where inWhse='0' ";
	$getLocQry .= "&& id='$id' && sentTo='$value' limit 1";

	$locRes = mysql_db_query($db,$getLocQry,$connection);

}

Thanks for any help.