Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
in pager_query function in pager.inc
a $count_query is generated by
$count_query = preg_replace(array('/SELECT.*?FROM/As', '/ORDER BY .*/'),
array('SELECT COUNT(*) FROM', ''), $query);
It will result in a significant peformance issue if there are too many fields.
I suggest to use the following to replace it. A signification performance improvement has been achieved on my site.
$count_query = preg_replace(array('/SELECT.*?FROM/As', '/ORDER BY .*/'),
array('SELECT COUNT(1) FROM', ''), $query);
I hope this can be fixed in the coming new release.
Comments
Comment #1
jian he CreditAttribution: jian he commentedstrange.
from MySQL Reference Manual:
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.
COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
Comment #2
thinker CreditAttribution: thinker commentedOops, forgot to metion that the db I am using is Postgres.
Comment #3
Cvbge CreditAttribution: Cvbge commentedshpchp: does this mean that count(1) can return different number then count(*)?
thinker: could you tell on which page in Drupal you can see to improvement?
I don't think this is a bug, so it won't be fixed in 4.6 ... OTOH 4.7 is near...
Comment #4
Cvbge CreditAttribution: Cvbge commentedthinker: also, it's been said on #postgresql that what you say is not possibile. Are you sure the performance increase was due to this change? What database version are you using? How can this be reproduced?
Comment #5
thinker CreditAttribution: thinker commentedTrue, I agree. However, it is common that we query more than one tables using join conditions with where clause, thus COUNT(*) will not always work as we expected.
I am NOT telling anything not being true ! As least COUNT(1) works for me on my site with my db containing thousands of records.
If you want to compare the performance change, find a query which returns thousands of records with large number of fields on your site, and change COUNT(*) to COUNT(1). You will see. You may also wish to calculate the time spent on queries using two different count methods.
I am using Postgres 7.4.6.
Comment #6
maggam CreditAttribution: maggam commentedTrying to understand this problem...what is the problem? What table are we trying to query, that can take a long time? Having a INDEX with primary key on the table may help...
Comment #7
moshe weitzman CreditAttribution: moshe weitzman commentedthe alleged problem is the regex, not a slow query
Comment #8
dopry CreditAttribution: dopry commentedDowngrading priority to normal... This *bug* doesn't break anything.
Comment #9
dopry CreditAttribution: dopry commentedSetting this task to won't fix. There is no bug involved, and there is no well defined goal to be accomplished. The original suggestions seems to have been flawed by the comments.
Comment #10
dopry CreditAttribution: dopry commentedcough. *status* cough.