As a viewer of your query (and not your database structure)there are certain steps I'd like to recommend:
1. There are sub queries that you have using IN/NOT IN. I'd recommend that you convert them to EXIST/NOT EXIST. You may have a look at this link:
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html[
^]
2. The other most interesting thing is that you will need to have covering indexes. Please read on covering indexes. Please go through the links provided by others and I'd also urge you to share the best possible links on this topic.
Regards
Pawan