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 "views_plugin_pager::execute_count_query" that Views automatically runs that does NOT contain any of my optimizations, like count queries ignore any hook overrides. Thus, in my case, the count query continues to run up a 13 second call, leaving me stuck with terribly slow page loads. (I've pasted the count query below in all its disgusting glory.)

So, how in blazes does one modify this query? Shouldn't any modification made to the actual query in the Views API be reflected in its associated count query anyhow?

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

Comments

dawehner’s picture

dawehner
Primary language German
commented

Can you please show us the code you used to optimize the query? maybe there's something obvious to spot on that.

Sinan Erdem’s picture

Sinan Erdem commented

I have a similar problem. I am using views to override taxonomy pages.

I have 300000 nodes that are categorized with some taxonomy terms.

When I try to load a category page, it is too slow. Here are the statistics from views reporting:

Query

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.sticky AS node_sticky, 'node' AS field_data_field_based_on_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
WHERE (( (node.status = 1 OR (node.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid
FROM
{taxonomy_index} tn
WHERE ( (tn.tid = '35') ))) ))
ORDER BY node_sticky DESC, node_created DESC
LIMIT 10 OFFSET 0

Title e-commerce
Path taxonomy/term/35
Query build time 4.18 ms
Query execute time 18028.61 ms
View render time 64.44 ms

When I look at the statistics with the devel module, I see that there are two queries that take the most time:

10072.62 ms -> views_plugin_pager::execute_count_query

SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM node node WHERE (( (node.status = 1 OR (node.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid FROM taxonomy_index tn WHERE ( (tn.tid = :db_condition_placeholder_0_0) ))) ))) subquery

10544.46 ms -> views_plugin_query_default::execute

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, node.sticky AS node_sticky, 'node' AS field_data_field_based_on_node_entity_type, 'node' AS field_data_body_node_entity_type FROM node node WHERE (( (node.status = 1 OR (node.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid FROM taxonomy_index tn WHERE ( (tn.tid = :db_condition_placeholder_0_0) ))) )) ORDER BY node_sticky DESC, node_created DESC LIMIT 10 OFFSET 0

The strange thing is it loads way slower for an anoymous user. When I try the same page with a logged in user, it takes far less time.

I know trying to display that many pages of content can be slow. But is there anything that I can do to fasten the things up? Maybe using a different pager, or putting a filter (just show last month's nodes, etc)?

dawehner’s picture

dawehner
Primary language German
commented

If it's slower for the guest user it's certainly a problem with node access, maybe you could get rid of that for the site?

Sinan Erdem’s picture

Sinan Erdem commented

I dont have any node access modules. I thought it takes longer because it tries to populate cache first, but apparently it isnt the source of the problem.

geerlingguy’s picture

geerlingguy commented

I've encountered this from time to time, too, though the execute_count_query calls rarely add more than 10-20ms to the page load time (on a table with more than a million records). It seems this result may be cached, though, for subsequent loads, because I only see that query show up now and then.

dawehner’s picture

dawehner
Primary language German
commented

If people do have issue with the performance of the pager I recommend to use http://drupal.org/project/views_litepager as it get's rid of the count query in general. Most people don't care about the last page anyway.

tim520’s picture

tim520 commented

If I use "Mini Pager" for my View, whether it will prevent from loading the last page ?