I need to write a MySQL query where it loops through every airport and generates 10 random offers (my offers table is 990,000+ rows)
The code I have below works for 1 airport, but as soon as it gets bigger it slows RIGHT down.
This is using PHP/MySQL
At the moment it takes well over a minute to run which is way too long -- I need it much, much quicker.
Can anyone help?
Code:
/**
* This code takes over a minute to run. How do I make it quicker?
*/
// Get airports
$sql0 = 'SELECT airportcode FROM airports ORDER BY airportcode ASC';
$query = $this->db->query($sql0);
// Loop through airports
foreach ($query->result() as $row)
{
// Get a random offer so long as the airport matches
$sql1 = 'SELECT T.id, T.DepAirportCode
FROM offers T
JOIN (
SELECT FLOOR( MAX( id ) * RAND( ) ) AS id
FROM offers
) AS x ON T.id >= x.id
WHERE
T.DepAirportCode="'.$row->airportcode.'"
LIMIT
10';
// Loop through random offers
$query1 = $this->db->query($sql1);
foreach ($query1->result() as $row)
{
print $row->id;
print $row->DepAirportCode;
} // next
} // next
