Using WP_Query only for result of SQL_CALC_FOUND_ROWS

[danielbachhuber]

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.

[boonebgorges]

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

[helen]

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.

[danielbachhuber]

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/

[boonebgorges]

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.

[danielbachhuber]

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