Improve Big Query Performance in WP

I was working on a site that had some custom posts and a lot of terms associated with that custom post type. After a while, we noticed that the search function is getting slower and slower, as the custom posts piled up. Sometimes, the time if took for the search to complete was more than a minute. Other times, it just gave back an error. So I knew I had do do something about this.

The thing is, when you have custom post types in WordPress, you’ll probably need at some point to make a custom query to the database. This can be quite easily done using the built-in class WP_Query, which can do just about anything you can think of. The problems start to appear when you have a relatively large database, and you do a big query on it.

What happens is that WP_Query returns an object that contains all the information about all the custom posts that you are querying. And trust me, every post contains a lot of information. So where is all this data stored after the query? In the RAM of course!

Our search function was doing just this: using WP_Query to get all the posts at once, then looping through them. When I did some tests to find out the memory usage of every search operation, I discovered that it uses quite a lot of RAM, and the amount increases with the search complexity and the number of results. The usage was often exceeding the allocated amount of 128 MB of memory per request.

The solution was to add an argument that limits the amount of data that is being stored in our query object, named fields.

// setup our args:
$args = array(
    'fields' => 'ids',
    // ... [other arguments that you need]

// get the query object that contains ids of posts:
$query = new WP_Query( $args );

// get only ids from object to loop them:
$ids = $query->posts;

// slightly rewrite the loop:
foreach( $ids as $id ){
    $post = get_post( $id );
    // ... [now do the other stuff with the $post]

The result

The result was amazing: memory usage improved 7 times, and execution time was 3 times faster for the usual searches, on average. The search function never crashed again, even though the posts kept multiplying, and continue to do so up until this day. And everyone lived happily ever after. The end.