Hello everyone

www\sites\all\modules\views\includes\view.inc uses such code to determine what number of records are there in a recordset, that the $count_query returns:

$count_query = 'SELECT COUNT(*) FROM (' . str_replace(array_keys($replacements), $replacements, $count_query) . ') count_alias';

(you can find it at the line 724)

The problem is it ALWAYS uses a subquery to get the data. Which means, that MySQL has to create a temporary table, copy some data there, and only count what is the total number of records. What I want - is the ability, to set $count_query without subquery. Which would be much faster btw.

So I suggest to reaplace that string with the following code:

 if (!preg_match('/select[^,]+count\(\*\)[^,]+from/i',$count_query))
    $count_query = 'SELECT COUNT(*) FROM (' . $count_query . ') count_alias';

What it does is checking if there any statement between "select" and "from" keywords, except for "count(*)" or "count(*) AS `some_alias_here`". And if it finds, that $count_query starts with "select count(*) from" than there is no reason to make a subquery for it. But if the SELECT statement includes some other keywords (which are seperated by comma, that's why i'm looking for non-comma statements in regexp), than the codes adds a subquery and it works just like it used to.

How can we use it:

function hook_views_pre_execute(&$view)
{
if($view && $view->name === '')
{
switch($view->current_display){
case '' :
$view->build_info['count_query'] ="SELECT count(*) FROM dru_node node WHERE (node.type in ('family_individual')) AND (node.status <> 0)";
/*
Which takes 0.08 sec
Instead of:
$view->build_info['count_query'] ="SELECT nid FROM dru_node node WHERE (node.type in ('family_individual')) AND (node.status <> 0)";
which will be transformed to:
SLEECT COUNT(*) FROM (SELECT nid FROM dru_node node WHERE (node.type in ('family_individual')) AND (node.status <> 0)) `count_alias`

0.2 sec

or even:

SELECT COUNT( * )
FROM (
SELECT node.nid AS nid
FROM dru_node node
INNER JOIN dru_family_individual family_individual ON node.nid = family_individual.nid
WHERE (node.type IN ('family_individual'))AND (node.status <>0)) count_alias

0.5-0.6 sec
*/
break;
}
}

}

CommentFileSizeAuthor
#2 total_number_of_records_optimised_count-views-1006810-2.patch825 bytessoul88
#1 total_number_of_records_optimised_count-views-1006810.patch758 bytessoul88

Comments

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented
StatusFileSize
new total_number_of_records_optimised_count-views-1006810.patch758 bytes

//Can't modify the issue for some reason, so here is the last code tagged properly

function hook_views_pre_execute(&$view)
{
  if($view && $view->name === '')
  {
    switch($view->current_display){
      case '' :
        $view->build_info['count_query'] ="SELECT count(*) FROM dru_node node WHERE (node.type in ('family_individual')) AND  node.status <> 0)";

/*
Which takes 0.08 sec

Instead of:
$view->build_info['count_query'] ="SELECT nid FROM dru_node node WHERE (node.type in ('family_individual')) AND (node.status <> 0)";
which will be transformed to:
SLEECT COUNT(*) FROM (SELECT nid FROM dru_node node WHERE (node.type in ('family_individual')) AND (node.status <> 0)) `count_alias`
0.2 sec

or even:

SELECT COUNT( * )
FROM (
SELECT node.nid AS nid
FROM dru_node node
INNER JOIN dru_family_individual family_individual ON node.nid = family_individual.nid
WHERE (node.type IN ('family_individual'))AND (node.status <>0)) count_alias

0.5-0.6 sec
*/
      break;
    }
  }

}

patch here:

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented
StatusFileSize
new total_number_of_records_optimised_count-views-1006810-2.patch825 bytes

Sorry for my distraction, here is the correct patch.

merlinofchaos’s picture

merlinofchaos commented
Version: 6.x-2.11 » 6.x-3.x-dev
Status: Needs review » Needs work

I don't understand this. In what situation would Views have already generated a count query as the prime query? As near as I can tell, this actually does nothing because Views won't normally generate the kind of query you're looking for.

The main reason that we use the subquery is that there are simply numerous instances (usually when group by or aggregation functions are involved) that the other method of COUNT()ing fails.

One thing that's new, since this method was created, is that we can now actually tell when aggregation is being used. So perhaps we can test on that instead, and maybe use the "old way" of generating a count query. But we have to be careful with that. It has to be bulletproof, which means it has to be tested with a LOT of different views to be sure that it won't provide false results.

In any case, this absolutely will not change for 2.x, so moving this to 3.x

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented

Views itself don't produce such a query. But the idea is to let a programmer to control the queries that are going to be executed. For example: I had a data list of about 80 000+ items that had to be displayed with pagination.

For some reason: 2 of them (queries) took 3 to 9 sec on my laptop.

The solution I found was the following:

function family_views_pre_execute(&$view)
{
  if($view && $view->name === 'family')
  {
    switch($view->current_display){
      case 'page_2' :

        $cnt=db_result(db_query("SELECT count(*) AS `cnt` FROM {node} node WHERE (node.type in ('family_individual')) AND (node.status <> 0)"));
        
        $curr_page = intval($_GET['page']);
        $per_page  = intval($view->pager['items_per_page']);
        $offset    = $curr_page*$per_page;
        $view->pager['offset']=($offset>$cnt)?0:-$offset;
        
        $view->build_info['query'] ="SELECT
       `family_individual`.`nid` AS nid,
       `family_individual`.`lastname` AS family_individual_lastname,
       `family_individual`.`firstname` AS family_individual_firstname,
       `family_individual`.`middlename` AS family_individual_middlename,
       `family_individual`.`gender` AS family_individual_gender,
       `family_individual`.`birthdate` AS family_individual_birthdate,
       `family_individual`.`deathdate` AS family_individual_deathdate
FROM (
   SELECT `nid`    
   FROM {family_individual} FORCE INDEX(`lastname`)
   JOIN {node} FORCE INDEX(`type`) USING(`nid`) 
WHERE `type` IN ('%s') AND `status` <> 0
ORDER BY `lastname` ASC
LIMIT $offset, $per_page
) AS `tmp`
JOIN {family_individual} AS `family_individual` USING(`nid`)
ORDER BY `family_individual_lastname` ASC";

       
       $diff=$cnt-$offset;
$view->build_info['count_query'] ="SELECT nid FROM {node} node WHERE (node.type IN ('%s')) LIMIT $diff";               
      break;

    }  
  }
 
}

Though I didn't hack any module, the solution is still pretty hackish, as for me.

The reason is the following:
1. Views always add db_query_range for the query. Which is not always suitable (when pagination is to be made in subquery).
2. Views don't react on all of the $view->pager array values, and always make a subquery from the count query.
for example: $pager_page_array = isset($_GET['page']) ? explode(',', $_GET['page']) : array();

So, now you can see the use case where the issue was born. And my vision of solving the second problem.

thnx for your reply.

IncrediblyKenzi’s picture

IncrediblyKenzi commented

I ran into this as well.. we had a view that had to parse 400k records.. on InnoDB this will take ages to generate a count even though we were only displaying 15 nodes at a time.

The workaround we had for non-views generated queries was to cache the query count on a per-block basis; right now there's no way to override the query that views generates for the count.

Perhaps a hook to override the count query would solve this?

damien tournoud’s picture

damien tournoud commented

Just a quick note to mention that the problem is not actually the subquery by itself, but the TEXT/BLOB columns that are pulled by it, because those forces MySQL to materialize the subquery.

Compare:

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM node WHERE status <> 0;
+----------+
| COUNT(*) |
+----------+
|   731970 | 
+----------+
1 row in set (0.54 sec)

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT * FROM node WHERE status <> 0) count_alias;
+----------+
| COUNT(*) |
+----------+
|   731970 | 
+----------+
1 row in set (3.07 sec)

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT nid FROM node WHERE status <> 0) count_alias;
+----------+
| COUNT(*) |
+----------+
|   731970 | 
+----------+
1 row in set (0.55 sec)
merlinofchaos’s picture

merlinofchaos commented

Sure there is. In hooks_views_pre_execute() change $view->build_info['count_query'] to whatever you want.

In 3.x, you also have pager plugins which can generate whatever count query they want.

rjbrown99’s picture

rjbrown99 commented

One note that I found after some trial and error -

$view->build_info['count_query'] (at least in the slightly older 3.x-dev version I am using), passes the query to db_rewrite_sql in the includes/view.inc execute() function. This db_rewrite_sql function also includes $this->base_table and $this->base_field.

Make sure your new query doesn't switch base_table or base_field. In my case I switched field, and the pager query then only works for users with elevated access rights.

maria_zk’s picture

maria_zk commented

Soul88 thank you SOOOO much for this!!!
It really saved the day!

mototribe’s picture

mototribe commented
Version: 6.x-3.x-dev » 7.x-3.3

I believe the count query could be further optimized by removing any table joins that don't have a WHERE clause.

For example, take a look at this monster count query - it takes 3500ms to run:

SELECT COUNT(*) AS expression FROM (/* photo_list view */ SELECT 1 AS expression FROM node node 
INNER JOIN users users_node ON node.uid = users_node.uid 
LEFT JOIN votingapi_cache votingapi_cache_node_points_vote_sum ON node.nid = votingapi_cache_node_points_vote_sum.entity_id AND (votingapi_cache_node_points_vote_sum.entity_type = :views_join_condition_0 AND votingapi_cache_node_points_vote_sum.value_type = :views_join_condition_1 AND votingapi_cache_node_points_vote_sum.tag = :views_join_condition_2 AND votingapi_cache_node_points_vote_sum.function = :views_join_condition_3) 
LEFT JOIN field_data_field_photo_topic field_data_field_photo_topic ON node.nid = field_data_field_photo_topic.entity_id AND field_data_field_photo_topic.field_photo_topic_tid = :views_join_condition_4 
LEFT JOIN history history ON node.nid = history.nid AND history.uid = :views_join_condition_5 
LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid 
WHERE (( (node.status = :db_condition_placeholder_0) )AND(( (node.type IN (:db_condition_placeholder_1)) AND (field_data_field_photo_topic.field_photo_topic_tid IS NULL ) )))) subquery

It shouldn't include:
LEFT JOIN votingapi_cache ...
LEFT JOIN history history ...
LEFT JOIN node_counter ....

Those JOINs are just used to get additional fields, not to restrict the view.

However, I see that it could get complicated figuring that out in views ...

mototribe’s picture

mototribe commented

This is a pretty slick solution:
http://drupal.stackexchange.com/questions/31444/how-do-i-optimize-a-view...

it's still not perfect, for example, I'm able to generate this count query:

SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM users u WHERE (status <> '0') ) subquery
which still takes 200ms to run (200k user records)

when the ideal query would be something like

SELECT COUNT(*) FROM users u WHERE (status <> '0')

which would probably take < 1ms to run.

Would it be possible to inject such an optimized count query into views?

soul88’s picture

soul88
Primary language Ukrainian
Location Ukraine
commented

mototribe, it's much improved in the version for D7. In D7 version first you get only the needed IDs which fit the conditions (so we have only joins on the tables we're filtering on). And after that we retrieve all the needed data by these IDs.

mustanggb’s picture

mustanggb commented
Title: patch: improving the query of total pages count » Improve the query performance of total pages count
Version: 7.x-3.3 » 7.x-3.x-dev
Component: Views Data » Code
Category: Task » Feature request
Issue tags: +Performance