multi table join performance problem

[john miller]

MySql (version: 4.0.22-standard) seems to be having a terrible performance problem.
I expected this to be a fairly simple, efficient dataset

I have 3 entities, and 2 intersection tables, for a total of 5 (core) tables


Actual stats follow (the MySql database is around 310 MB)

492 club records
index by club_id

1200 member records
index by member_id

2249 member_club (member_id, club_id)
index on member_id
index on club_id

685575 associates (average record = 491 bytes)
index by associate_id

981413 member_associate (member_id, associate_id)
index by member_id
index by associate_id


Now, here is the issue:

select
c.club_id, c.name,
m.member_id, m.name,
a.associate_id, a.name
from
club c
inner join member_club mc on
mc.club_id = c.club_id
inner join member m on
m.member_id = mc.member_id
inner join member_associate ma on
ma.member_id = m.member_id
inner join associate a on
a.associtate_id = ma.associate_id
where
c.club_owner = 35


This basic query is at the heart of the system, and has to be run several thousand times a day.
Further, the data updates that manipulates these relationships run several thousand times a day too.

MySql is taking 30 to 45 seconds to run it and it takes less than 1 second on a Microsoft SQL/Server server. The two servers (MySql is a hosted web server w/ CPanel, and SQL/Server is an old workstation runnnig MSDE) are not identicial, but lets allow for server load and say the SQL/Server took 3 seconds... the difference is still extreme!!!!

I just can't imagine I'm at the limits of MySql! Any ideas?