How do I optimize a Views "execute_count_query"?
As long as you know the query you want use in its place, you can override the count query for a view fairly easily. There are probably other places to do it but I found hook_views_pre_execute() to work pretty well:

function MYMODULE_views_pre_execute($view) {
  if ($view-&gt;name == 'view_name' &amp;&amp; $view-&gt;current_display == 'page') {
    $new_count_query = db_select('some_table', 't')
      -&gt;fields('t', array('id'))
      -&gt;condition('some_field', 'some_condition');

    $view-&gt;build_info['count_query'] = $new_count_query;
  }
}


Views will convert the above query into a COUNT(*) at some point further down the line.