This is yet another spin-off from #681760: Try to improve performance and eliminate duplicates caused by node_access table joins.

Currently, a pager query countQuery will force a subselect on the COUNT in order to reduce duplicate node counts. However, by optimizing the query properly, we can eliminate the need for such a hack.

If we run the countQuery for SelectQuery identically to that in SelectQueryExtender, then we can optimize node access queries with the query cache running.

CommentFileSizeAuthor
#5 1011706-countQuery.patch3.59 KBagentrickard
#1 1011706-countQuery.patch3.59 KBagentrickard

Comments

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
Status: Active » Needs review
StatusFileSize
new 1011706-countQuery.patch3.59 KB

And a patch. The logic here is that if the COUNT query matches the syntax of the base query, then query caching kicks in for a nice win.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

Testbot fail.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
Status: Needs review » Needs work

Force testbot.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
Status: Needs work » Needs review

And again.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented
StatusFileSize
new 1011706-countQuery.patch3.59 KB

Bad testbot crash.

Status: Needs review » Needs work

The last submitted patch, 1011706-countQuery.patch, failed testing.

Stevel’s picture

Stevel commented

Looks like we need the field to stay also if an alias to the field/expression is in the group by clause.

agentrickard’s picture

agentrickard
he/him
Primary language English
Location Georgia (US)
commented

That didn't make any sense.

Stevel’s picture

Stevel commented

Sure it does: SELECT Count(*), field_name as field_name_alias FROM test_table GROUP BY field_name_alias

damien tournoud’s picture

damien tournoud commented
Status: Needs work » Closed (won't fix)

The premise of this bug report is false:

If we run the countQuery for SelectQuery identically to that in SelectQueryExtender, then we can optimize node access queries with the query cache running.

That's not true in any version of MySQL. We studied that at length in #423888: Use subqueries for ->countQuery(), at least for MySQL.

damien tournoud’s picture

damien tournoud commented

Also see a long discussion in #778050: Add support for database hints and make PagerDefault properly pluggeable why we haven't followed the SQL_CALC_FOUND_ROWS route in Drupal 7 (or why one query + one count query that never changes can be better then only one more costly query).