[eluser]alitech[/eluser]
Pardon my ignorance but what is a "join"? I can see now after your comments that it makes an unusually high number of requests and also uses a lot of memory.
When SERPs come on to the site, they kill it completely.
You also mention its a design issue? Design as in UI or designing the DB / Codebase?
I think there may be references to queries that are not really required on the homepage. I am not sure about the repititions but yes, it seems logical to remove them.
To pose a question to you all, once you activate profiling on your sites, how many requests do you usually see?
My site has a lot of content and a lot of pages but I dont consider the site to be heavyweight. What should be the number of requests for a site my size compared to a heavyweight site?
