MySQL: Random offers for every airport

Applications Web Development
1

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?

/**
 * 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
2

You need to combine the two queries into 1 query and so that at least the WHERE clause looks as follows:
WHERE T.DepAirportCode=airports.airportcode
Have a read of this to select a random row: SQL to Select a random row from a database table

3

Many thanks, I have resolved the issue by another means.

Thanks again!