Create Index - Speed Issues

[Borderline]

Afternoon

 

I'm working on producing a series of pages that pull data from several databases. I've generated an index on the data (raceid), as when I first joined the tables, the resulting page was horribly slow to load. Testing in this instance was on a limited number of rows (approx 750) on a different server, and the result was excellent.

 

I've since finished the data collation, which has resulted in 11,969 rows in the main database. I've tried a similar code on this dataset but despite the index, the page fails to load and provides an error message: Internal Server Error - The server encountered an internal error or misconfiguration and was unable to complete your request. The result loaded fine with the horse and jockey joins, it was only when the third join was added that the issue occurred.

 

I was wondering whether anyone could advise me on how to proceed - am I asking for an unrealistic result, or is there an issue with my code? Is there a chance the change of webhost could be a problem - my current hosting is shared, whereas my previous was a VPS. I attached below the current SQL, and would be grateful for any advice/pointers.

$result = mysqli_query($con,"SELECT 	runners.raceid, runners.pos, runners.horse, runners.eqp, runners.age, runners.weight,					runners.rat, runners.dist, runners.beat, runners.jockey, runners.trainer,					horse.horse, horse.h_ctry, horse.h_link,					jockey_career.jockey, jockey_career.careerlink,					trainer_career.trainer, trainer_career.careerlink														FROM 		runners			INNER JOIN      horse			ON 		runners.horse = horse.horse									 			INNER JOIN      jockey_career			ON 		runners.jockey = jockey_career.jockey  			INNER JOIN      trainer_career			ON 		runners.trainer = trainer_career.trainer									 			WHERE		runners.raceid = 2000001			ORDER BY	runners.runid");

Thanks

Charlie

[davej]

I guess I would wonder whether you need the website to display the live table data? What if you instead periodically updated a temporary table and displayed that one table?

[Borderline]

Many thanks for the speedy response. I've never looked into temporary tables - will go have a read up to see what the score is. Thanks!

[justsomeguy]

You can go into phpMyAdmin and use an EXPLAIN EXTENDED query to have the database server show you how it is doing the joins and what keys it is using, if any. Every join should be with keys, if you have joins that are not using keys then that's a problem. Just write EXPLAIN EXTENDED before your select query and you'll see the output from MySQL.