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

jian he’s picture

strange.

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.

thinker’s picture

Oops, forgot to metion that the db I am using is Postgres.

Cvbge’s picture

Version: 4.6.3 » x.y.z
Assigned: Unassigned »

shpchp: 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...

Cvbge’s picture

thinker: 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?

thinker’s picture

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.

True, 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.

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?

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.

maggam’s picture

Trying 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...

moshe weitzman’s picture

the alleged problem is the regex, not a slow query

dopry’s picture

Priority: Critical » Normal

Downgrading priority to normal... This *bug* doesn't break anything.

dopry’s picture

Setting 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.

dopry’s picture

Status: Active » Closed (won't fix)

cough. *status* cough.