I have a query that returns about 1,500 records. The usual thing is to use LIMIT $offset,$limit to control the number of records returned per page. Unfortunately, this application requires that all records be shown on one page.
My query works, but it takes at least 15 minutes to return the 1,500 records. I was just wondering if there was a different way to write the query to cut down on the amount of time it takes to run.
Query explanation:
1. I run the main query, which finds 1,500 records.
2. I then iterate through each record.
3. Each iteration (record) has four other queries inside it.
4. So basically, I'm running 4 x 1,500 queries = 6,000 queries total.
Here is the code:
$qry = "select mD.*,mC.category as cat,mT.mType as type
from movieDescr mD, mediaCategories mC, mediaType mT
where
mD.category=mC.id
&& mT.id=mD.type
order by mC.category ";
$result = mysql_db_query($db,$qry,$connection) or die (mysql_error());
while($row=mysql_fetch_array($result)) {
$id = $row['id'];
$title = $row['title'];
$mediaFormat = $row['type'];
$cat = $row['cat'];
/////////////////////////////get total copies
$getCopies = "select count(id) from movies where id='$id'";
$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' ";
$r = mysql_db_query($db,$getCopies,$connection);
$availCount = mysql_result($r, 0);
//////////////////////////get history
$getHistQry = "select (TO_DAYS(now()) - TO_DAYS(returnDate)) as numDays
from history
where
titleId='$id' limit 1";
$histRes = mysql_db_query($db,$getHistQry,$connection);
$numRows = mysql_num_rows($histRes);
$histResRow = mysql_fetch_object($histRes);
$daysCount = $histResRow->numDays;
/////////////////////// does store currently have movie?
$getLocQry = "select count(id) from movies
where
inWhse='0' &&
id='$id' && sentTo='$value' limit 1";
$locRes = mysql_db_query($db,$getLocQry,$connection);
$icon=(mysql_result($locRes,0)==1?'in_store.gif':$icon);
$output .= 'STATEMENTS HERE...';
}
echo $output;
?>
TIA,
Richie.
