Site Is So Slow!

[eluser]dmorin[/eluser]
Cool, that helps a lot. So a good exercise when starting with explain is to multiply all of the numbers in the "rows" column together. This is the max number of rows that mysql has to look through the get the result. Because you have a where it won't be quite this high but it's still a good starting point. In your case, it's 803,387,392,390. This starts to explain why your site is a little on the slower side.

So to start, if you're comfortable changing the sql schema, make sure that you have indexes on each of these fields:

meet_events.id
meet_events.meet_id
meet_events.event_id
events.id
meet_athletes.meet_event_id
meet_athletes.athlete_id
athletes.id
roster.athlete_id
roster.coach_id
coaches.id

I'll be the first to admit that I'm not the best at identifying which fields need indexes, and someone might come along and say having one on one of these fields won't help, but because these are all involved in your join or where statements, it's a pretty safe bet that all of these should have indexes.

Primary key fields should obviously have the Primary key type. If it should be unique use that kind, otherwise just use a plain index. If you have a development server to do it on first, that's good. After you've made the changes, rerun the explain query and post back here.

Hope this is helping. Let us know if you've had enough feedback and want us/me to stop or if you're ok to keep going :-)

Edit: for more info, check out http://hackmysql.com/case4 - Decent article on joins