WordPress queries are already pretty optimized out of the box, taking advantage of caching mechanisms when available. It’s important that WordPress do this considering the amount and type of data that a typical query returns. For example, let’s take a look at the return data from this query:
$query = get_posts( array( 'posts_per_page' => 1 ) );
This is a super simple query. We are just returning the most recent post from our blog. Here is the data that is returned:
Array (  => WP_Post Object ( [ID] => 1 [post_author] => 1 [post_date] => 2012-09-25 20:44:47 [post_date_gmt] => 2012-09-25 20:44:47 [post_content] => Welcome to WordPress. This is your first post. Edit or delete it, then start blogging! [post_title] => Hello world! [post_excerpt] => [post_status] => publish [comment_status] => open [ping_status] => open [post_password] => [post_name] => hello-world [to_ping] => [pinged] => [post_modified] => 2012-10-16 17:22:34 [post_modified_gmt] => 2012-10-16 17:22:34 [post_content_filtered] => [post_parent] => 0 [guid] => http://localhost:8888/trunk/?p=1 [menu_order] => 0 [post_type] => post [post_mime_type] => [comment_count] => 1 [filter] => ) )
Look at all that data for one post (and that’s not to mention the meta and term data that has been referenced as well). Since WordPress takes advantage of caching, we get solid performance from typical queries like this. But when you start using WordPress for advanced queries with lots of posts to thumb through (e.g. 10k posts), your typical speedy query can become a resource hog in a hurry.
Optimizing the query
Thankfully there are ways in which we can get around some of these performance bottlenecks in the query when dealing with a large number of posts. Let’s examine a few.
If You Dont’ Need Pagination, Don’t Do It!
Not every query needs pagination. WordPress uses SQL_CALC_FOUND_ROWS when dealing with pagination…on every query, whether you need pagination or not.
There is a general consensus that while SQL_CALC_FOUND_ROWS is convenient and only accounts for one query, it is noticeably slower than using the extra query COUNT, especially when we have already set our WHERE, ORDER and LIMIT clauses (which WordPress does).
So to bypass this, there is a nifty parameter called ‘no_found_rows’ where we can turn this off altogether.
Remember, your query must not rely on pagination to make use of the parameter; otherwise, your pagination will break.
Here is how we implement it:
$query = new WP_Query( array( 'posts_per_page' => 1, 'no_found_rows' => true ) );
Just Post IDs
What if you only need a list of post IDs? It doesn’t make sense to get all that data that is normally returned from get_posts if we just need an array of post IDs.
Fortunately for us, we can utilize yet another parameter called ‘fields’ and specify that we only want post IDs, like so:
$query = get_posts( array( 'posts_per_page' => 1, 'fields' => 'ids' ) );