Site Is So Slow!

[eluser]Hockeychap[/eluser]
Have had a quick look at the model. Couple of things jump out:

1. Think there's a typo in the code.

Code:

LEFT JOIN meet_athletes AS Meet_Athlete ".
                    "ON Meet_Event.id = Meet_Athlete.meet_event_id ".

In the left hand side of the join condiion you have a field called Meet_Event.id. In all the other cases, the field is linked on Meet_Event.Event_Id. So can you check that your table Meet_Events actually has a field called ID. My guess is that the line should read:

Code:

LEFT JOIN meet_athletes AS Meet_Athlete ".
                    "ON Meet_Event.Event_id = Meet_Athlete.meet_event_id ".

2. A six table chain to get a couple of counts is probably a little excessive. In version 3.x of Mysql there was about a 3 table limit before performance pitched. You're going to be near to a performance limit (will have to try and simulate this at home tomorrow).

NB. I will caveat that with the fact that the design of the tables seems to be correct from a dba viewpoint.

3. As I think I said earlier, check the tables for indicies on the keys that you are joining on. If I've read your sql properly, can you check that have an index on each of the following:

Meet_Athletes.Meet_Event_ID
Events.Event_ID
Athletes.Athlete_ID
Roster.Athlete_ID
Coaches.Coach_ID

4. If the indicies don't help, then I would start looking into using sub-queries on the section of the query dealing with the athletes and their coaches.

5. If after all of this, you;re queries are still running unacceptably slowly, then it will be worth fracturing your query into two or three parts and loading them into a number of php arrays (as array key lookups in PHP is very fast). I would only do this as a final step.

As a general comment, bar the number of joins, this is not a complex query and should be well withing MYSQLs capability, even on a shared hosting platform.

If you need any help with checking the tables or want me to have a look at the actual query in action, post a note back and I'll explain how to extract and zip up the data so that I can load it onto a spare server I manange.

Hope this helps,

Cheers
Justin