Make WordPress Core

Opened 11 years ago

Last modified 6 years ago

#29178 new enhancement

Using WP_Query only for result of SQL_CALC_FOUND_ROWS

Reported by: danielbachhuber's profile danielbachhuber Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Query Keywords: needs-patch
Focuses: Cc:

Description

For certain web hosts who reject direct SQL queries and push for use of WP_Query everywhere, it would be nice if you could use WP_Query only for the result of SQL_CALC_FOUND_ROWS

My use case is that I'm added limited faceting support to a search interface. For each facet, I'd like to indicate the number of matching results. Using update_post_meta_cache => false and update_post_term_cache => false means using WP_Query still produces two queries.

Also, it would be interesting to compare the performance of SQL_CALC_FOUND_ROWS vs COUNT(*) when all you care about is the total count.

Change History (5)

#1 @boonebgorges
11 years ago

Using update_post_meta_cache => false and update_post_term_cache => false means using WP_Query still produces two queries.

What two queries? SELECT ... ID and FOUND_ROWS()? Not sure that SELECT SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be separated from each other :) Or are you talking about the query that fills in post data for the located IDs? This can be skipped by passing 'fields' => 'ids'.

#2 @helen
10 years ago

  • Keywords reporter-feedback close added; needs-patch needs-unit-tests removed

If you want to run those numbers for COUNT, that would be cool. Boone is correct, though - SQL_CALC_FOUND_ROWS and FOUND_ROWS() will be two queries.

#3 @danielbachhuber
10 years ago

  • Keywords needs-patch added; reporter-feedback close removed

Feeling a bit deja vu on this conversation. I think Boone and I chatted on Skype about this, and never summarized here.

For the purposes of solely getting a total count, SELECT COUNT(*) is fastest, and is one query. SELECT COUNT(*) is also faster when used as a replacement for SQL_CALC_FOUND_ROWS: http://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

#4 @boonebgorges
10 years ago

danielbachhuber - Yes, I think that's the gist of it.

As a workaround, you can do the following:

$q = new WP_Query( array(
     'fields' => 'ids',
     'posts_per_page' => -1,
     'no_found_rows' => true,
) );
$count = count( $q->posts );

I'm not sure how much overhead this adds beyond SELECT COUNT(*). A little extra memory is required to store the post IDs.

#5 @danielbachhuber
10 years ago

Yeah... but VIP is going to flip on 'posts_per_page' => -1 :(

Note: See TracTickets for help on using tickets.