WP_Query is fast until it is not

A property site I took over had a search page that took fourteen seconds. The query looked reasonable — filter by city, price range and number of bedrooms, sorted by price. Four arguments. It was generating a query with three self-joins against a postmeta table holding 2.4 million rows.

Nothing in the WP_Query documentation warns you about this, and that is the problem. The API makes cheap things and catastrophically expensive things look identical.

Why postmeta is the villain

WordPress stores custom fields in one long, narrow table: post id, meta key, meta value. Every additional meta condition in a query is another JOIN against that same table.

new WP_Query([
    'post_type'  => 'property',
    'meta_query' => [
        ['key' => 'city',     'value' => 'Dubai'],
        ['key' => 'bedrooms', 'value' => 3, 'compare' => '>='],
        ['key' => 'price',    'value' => [500000, 900000], 'compare' => 'BETWEEN'],
    ],
]);

That is three joins. And meta_value is a LONGTEXT column, so MySQL can only index the first part of it — a numeric comparison on a text column means casting every candidate row before comparing it.

'compare' => '>=' on a text column comparing numbers is the specific thing that turns a fast site slow. '100' is less than '99' as a string, so you also need 'type' => 'NUMERIC', which forces the cast on every row and removes any remaining chance of using an index.

If you filter or sort by it, it should be a taxonomy or its own table — not a meta field. Taxonomies are properly indexed and designed for exactly this. Meta is for data you display after you have already found the post.

The arguments that cost you

posts_per_page => -1. Every post, hydrated into objects, in memory. Fine on a site with 40 posts. A fatal error on a site with 40,000, usually two years after it was written.

'orderby' => 'meta_value_num'. Sorts by a cast of a text column. Combined with a meta filter, it is the slowest thing you can write in WordPress without trying.

's' => $term. Core search is LIKE '%term%' across three columns, with a leading wildcard, so no index applies. It is acceptable on a small blog and hopeless on anything else. Use a real search index.

'tax_query' with 'operator' => 'NOT IN'. Negation cannot use the index, and forces a scan of the term relationships table.

'no_found_rows' => false when you do not need pagination. WordPress runs a second SQL_CALC_FOUND_ROWS query to count the total. If there is no pager on the page, that query is pure waste:

new WP_Query([
    'post_type'      => 'post',
    'posts_per_page' => 5,
    'no_found_rows'  => true,          // skip the count query
    'update_post_meta_cache' => false, // skip if you only need titles
    'update_post_term_cache' => false,
]);

Those three lines on a “recent posts” widget are free and I add them by reflex.

fields => 'ids' is underrated

If you only need to know which posts matched, do not hydrate them:

$ids = get_posts([
    'post_type'      => 'property',
    'posts_per_page' => 500,
    'fields'         => 'ids',
    'no_found_rows'  => true,
]);

One column, no post objects, no meta cache warmed. Then _prime_post_caches( $ids ) when you actually need the content, which fetches everything in one go rather than one query per post.

When to drop to SQL

The WordPress community treats $wpdb as a last resort. I think that is backwards for reporting and bulk work.

global $wpdb;

$rows = $wpdb->get_results( $wpdb->prepare( "
    SELECT p.ID, p.post_title, pm.meta_value AS price
    FROM {$wpdb->posts} p
    INNER JOIN {$wpdb->postmeta} pm
        ON pm.post_id = p.ID AND pm.meta_key = 'price'
    WHERE p.post_type = 'property'
      AND p.post_status = 'publish'
      AND CAST(pm.meta_value AS UNSIGNED) BETWEEN %d AND %d
    ORDER BY CAST(pm.meta_value AS UNSIGNED) ASC
    LIMIT 20
", 500000, 900000 ) );

One query, one join, columns you chose. You lose the filters other plugins hook into, which matters on a public archive and does not matter at all on an admin report. Know which one you are writing.

Always prepare(). Always.

The structural fix

For anything with real filtering, stop fighting the schema and add your own table.

// One row per property, columns you actually filter on, properly indexed.
CREATE TABLE wp_property_index (
  post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  city VARCHAR(80) NOT NULL,
  bedrooms TINYINT UNSIGNED NOT NULL,
  price INT UNSIGNED NOT NULL,
  KEY city_price (city, price),
  KEY city_beds_price (city, bedrooms, price)
);

Keep it in sync on save_post, query it directly, then load the posts by id. A search that was fourteen seconds became about sixty milliseconds. It is roughly a day of work and it is the correct answer more often than the WordPress ecosystem admits.

Caching is not the fix

It is worth saying plainly, because it is the first suggestion in every thread: object caching and page caching make a slow query invisible, not fast. The first request after every save still pays full price, and on a site where content changes constantly that is a lot of requests.

Cache after you have made it fast. Caching instead of making it fast is how you end up with a site that is quick for everyone except the editors who use it all day.