Slow SQL_CALC_FOUND_ROWS Query

Question

We are experiencing very slow performance with queries that use SQL_CALC_FOUND_ROWS within the admin section of WordPress.

We currently have about 125,000 posts on our site and use Varnish to cache the front-end and are on WordPress version 4.2.3.

The problem arises when there are people using the admin section of WordPress and WordPress will run a query like the one below:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
WHERE 1=1 AND (((wp_posts.post_title LIKE '%denali%') 
OR (wp_posts.post_content LIKE '%denali%'))) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' 
OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' 
OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_title LIKE '%denali%' DESC, 
wp_posts.post_date DESC LIMIT 0, 20 

Is there a patch to fix this issue or some sort of pre_get_posts filter I can run?

I plan on removing some post revisions and doing some DB optimization, but first wanted to see if there was some sort of fix for this within WordPress.

I have come across similar issues while searching for this, but most of those issues seem to be 2-6 years old.

Answer 1

The use of SQL_CALC_FOUND_ROWS is not really a problem, although it incurs an overhead.

What happens is, WordPress uses SQL_CALC_FOUND_ROWS in order to determine the total posts that would have been returned, if no LIMIT clause was provided. This is necessary in order to calculate and provide you with correct pagination links.

Disabling it unconditionally is guaranteed to break things all over the place.

If you can identify specific queries that suffer from its use, and you can do without pagination, then you could hook to pre_get_posts and conditionally set the no_found_rows parameter to true. This, however, is more a hack than a solution.

The proper solution is to use a database query caching mechanism, either on the side of the database, or on the WordPress side using a plugin such as Advanced Post Cache (developed for and used in WordPress.com)

Answer 2

$query->set('no_found_rows', true);