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

I have a site that is dragging big time. I need to find someone to identify what is causing some of the problems and fix. I'm in Snohomish and wish to use someone local. The developer I hired to build the site is in NJ and time difference got to be too hard. Please contact me with rates and availability if interested and I will provide more information on the project.