MySQL optimization

Over and over again mysql slow quries log this query taking a long time.
SELECT DISTINCT COUNT(DISTINCT(n.nid)) FROM main_node n LEFT JOIN main_comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS
NULL) INNER JOIN main_node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid
= 2 AND na.realm = 'workflow_access') OR (na.gid = 3128 AND na.realm = 'workflow_access_owner') OR (na.gid = 0 AND na.realm = 'og_pu
blic') OR (na.gid = 2205 AND na.realm = 'og_subscriber'))) AND ( n.status = 1 AND (n.uid = 3128 OR c.uid = 3128));

It is well-known that many very useful queries that you create (on your own or via the Views module) are costly to execute because MySQL creates a temporary table and does a filesort. A common example is {node}.type='xxx' and {node}.status='yyy' order by {node_comment_statistics}.comment_count desc.

Another common example is sorting posts in reverse chron order of most recent comment timestamp, which the Views module implements as order by GREATEST({node}.changed, {node_comment_statistics}.last_comment_timestamp) DESC