So, I was able to optimize a particularly nasty query in one of my views using mymodule_views_query_alter(), bringing it from 13 seconds down to about 200 milliseconds. However, I see in my Devel query log there is an associated "execute_count_query" that Views automatically runs that does NOT contain my optimizations. It is as if any count query ignores my hook overrides. Thus, the count query continues to run up a 13 second call! I've pasted it below in all its disgusting glory.
So, how in blazes do I get at this query?
SELECT COUNT(*) AS expression FROM (
SELECT 1 AS expression FROM drupal_node node INNER JOIN (
SELECT td.*, tn.nid AS nid FROM drupal_taxonomy_term_data td
INNER JOIN drupal_taxonomy_vocabulary tv ON td.vid = tv.vid
INNER JOIN drupal_taxonomy_index tn ON tn.tid = td.tid
WHERE (tv.machine_name IN (:db_condition_placeholder_7)) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
LEFT JOIN drupal_taxonomy_vocabulary taxonomy_term_data_node__taxonomy_vocabulary ON taxonomy_term_data_node.vid = taxonomy_term_data_node__taxonomy_vocabulary.vid
LEFT JOIN drupal_field_data_field_published_at field_data_field_published_at ON node.nid = field_data_field_published_at.entity_id AND (field_data_field_published_at.entity_type = :views_join_condition_0 AND field_data_field_published_at.deleted = :views_join_condition_1)
WHERE (( (node.status = :db_condition_placeholder_0) AND (node.nid IN (SELECT tn.nid AS nid FROM drupal_taxonomy_index tn
LEFT OUTER JOIN drupal_taxonomy_term_hierarchy th ON th.tid = tn.tid
LEFT OUTER JOIN drupal_taxonomy_term_hierarchy th1 ON th.parent = th1.tid
LEFT OUTER JOIN drupal_taxonomy_term_hierarchy th2 ON th1.parent = th2.tid
LEFT OUTER JOIN drupal_taxonomy_term_hierarchy th3 ON th2.parent = th3.tid
LEFT OUTER JOIN drupal_taxonomy_term_hierarchy th4 ON th3.parent = th4.tid
WHERE ( (tn.tid = :db_condition_placeholder_1) OR (th1.tid = :db_condition_placeholder_2) OR (th2.tid = :db_condition_placeholder_3) OR (th3.tid = :db_condition_placeholder_4) OR (th4.tid = :db_condition_placeholder_5) ))) )AND(( (node.type IN (:db_condition_placeholder_6)) )))) subquery