multi table join performance problem
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?
Subject
Views
Written By
Posted
multi table join performance problem
8214
May 12, 2005 12:06PM
4172
May 12, 2005 02:12PM
3850
May 13, 2005 01:17AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.