4

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
  • it might help if you post your optimized views query, the count query should be very similar. I'm also interested in your hook_views_query_alter() code snippet since I'm struggling to get it to work myself. Thanks! – uwe May 24 '12 at 23:33
  • 2
    Try to abandon terms hierarchy this is a major complication of the query. – Daniel Wehner May 30 '12 at 16:33
2
+50

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->name == 'view_name' && $view->current_display == 'page') {
    $new_count_query = db_select('some_table', 't')
      ->fields('t', array('id'))
      ->condition('some_field', 'some_condition');

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

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

|improve this answer|||||
0

Try not to use "Content: Has taxonomy terms (with depth)" filter - use "Content: Has taxonomy term" instead and SELECT sub-query will not be used. This will dramatically increase performance of SQL-query.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.