Improving loading times

[Borderline]

I'm trying to generate a raceform site, with the results page displaying the horse, jockey and trainer as links which lead to their individual pages. I have the right effect, but it's taking a long time to load the page, which I'm assuming is due to the number of joined tables showing different data.

http://www.further-flight.co.uk/site/test/data/races/200106.php

I wondered if anyone had ideas for improving the code/loading time?

Original code:

//  Get all the data from the table
    $result = mysql_query(" SELECT      runners.raceid, runners.pos, runners.dist, runners.horse,  runners.eqp, runners.draw, runners.age, runners.weight, 
                                        runners.overw, runners.rat, runners.jockey, runners.trainer,
                                        race.raceid, race.link, race.dateshort, race.cshort, race.classshort, race.distf, race.ground, race.racetype, 
                                        race.run,
                                        horses.horse, horses.hlink,
                                        connectionstest.jockey, connectionstest.trainer, connectionstest.con_link
                            FROM        runners 
                                        Inner Join race On
                                        runners.raceid = race.raceid 
                                        Inner Join horses On
                                        runners.horse = horses.horse
                                        Inner Join connectionstest On
                                        runners.jockey = connectionstest.jockey
                            OR          runners.trainer = connectionstest.trainer                   
                            WHERE       runners.raceid = '2001006'
                            ORDER BY    runners.id") 
                            or die(mysql_error());  

        echo    "<table>";
        echo    "<tr>
                <th>Pos</th> <th>Dist</th> <th>Horse</th> <th>Eqp</th> <th>Draw</th> <th>Age</th> <th>Weight</th> <th>Rat</th>
                <th>Jockey</th> <th>Trainer</th></tr>";

//  Keeps getting the next row until there are no more to get

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

        include($_SERVER['DOCUMENT_ROOT'].'/site/test/includes/links.php');     

//  Print out the contents of each row into a table
        echo    "<tr><td>";
        echo    $row['pos'];
        echo    "</td><td>";
        echo    titleCase($row['dist']);
        echo    "</td><td>";
        echo    "<a href='$horselink'>"; 
        echo    titleCase($row['horse']);
        echo    strtoupper($row['ctry']);       
        echo    "</td><td>";
        echo    $row['eqp'];
        echo    "</td><td>";
        echo    titleCase($row['draw']);
        echo    "</td><td>";
        echo    titleCase($row['age']);
        echo    "</td><td>";
        echo    $row['weight'];
        echo    " ";
        echo    $row['overw'];
        echo    "</td><td>";
        echo    $row['rat'];
        echo    "</td><td>";      
        echo    "<a href='$jockeylink'>";                
        echo    titleCase($row['jockey']);
        echo    "</td><td>";
        echo    "<a href='$trainerlink'>";           
        echo    titleCase($row['trainer']);     
        echo    "</td></tr>";
        }
        echo    "</table>";                          

I've attempted to collate the jockey and trainer links into a single table, and while it loads far more quickly (links not yet working) it's not producing the results correctly:

http://www.further-flight.co.uk/site/test/data/races/2001006_1.php

Database table: http://www.further-flight.co.uk/site/test/data/races/connectionstest.jpg

Code for second version:

//  Get all the data from the table
    $result = mysql_query(" SELECT      runners.raceid, runners.pos, runners.dist, runners.horse,  runners.eqp, runners.draw, runners.age, runners.weight, 
                                        runners.overw, runners.rat, runners.jockey, runners.trainer,
                                        race.raceid, race.link, race.dateshort, race.cshort, race.classshort, race.distf, race.ground, race.racetype, 
                                        race.run,
                                        jockey.jockey, jockey.jlink,
                                        horses.horse, horses.hlink,
                                        trainer.trainer, trainer.tlink
                            FROM        runners 
                                        Inner Join race On
                                        runners.raceid = race.raceid
                                        Inner Join jockey On
                                        runners.jockey = jockey.jockey 
                                        Inner Join horses On
                                        runners.horse = horses.horse        
                                        Inner Join trainer On
                                        runners.trainer = trainer.trainer                                   
                            WHERE       runners.raceid = '2001006'
                            ORDER BY    runners.id") 
                            or die(mysql_error());  

Does anyone have any thoughts on how I can get the right effect without killing off my database? Any advice gratefully received.

[JorgeM]

Does the data have to be queried in real time? If it doesn't, what I've done in the past is have a table will is filled by a SQL job on a schedule (once per day, per hour, etc..). Then instead of calling the complex SQL query, you just query the table that already has the pre-queried data. If the data doesn't change that often, there is no need to query it in real time.

[diafol]

You're selecting 26 fields, but not using them all. Having multiple inner joins is a bit slow, but it shouldn't be that slow, unless you've got thousands of entries. Are your 'foreign keys' indexed? If not, try indexing them. If you have very large tables, you may find that subqueries could help you in limiting the search, but as a rule subquering is generally slower.

Your tables should be normalized, so trying to shoehorn them into tables that don't make sense is a not recommended.

[Borderline]

Many thanks, will look into these suggestions.