How to speed up wp_query, took more 5s to run against 100k posts

by

Here’s the culprit:

[meta_query] => Array
    (
        [0] => Array
            (
                [key] => city
                [value] => Las Vegas
                [compare] => =
            )

    )

Meta queries are slow, and the only way to speed them up is to not use meta queries.

The post meta table is optimised for fetching key/value pairs where the post ID is already known. That’s why get_post_meta is fast.

But for searching and filtering out posts, or finding/searching posts by their meta, the performance is extremely poor. This is by design, so that fetching post meta is fast.

So how do you filter/search/find posts based on their data?

Taxonomies

The taxonomy tables are designed for finding posts when you know the a term ID/name. This is how tags and categories are built, and why they’re so much faster than meta queries.

The root problem is that your city post meta should have been a custom taxonomy. Moving to a custom taxonomy will improve your query speed by orders of magnitude.

Remember:

  • If you need to list search query group or filter for posts by information, use a custom taxonomy
  • If you are only using the information when you already have the post, e.g. you’re displaying a post, and you don’t need to filter/query for that data, use post meta

And don’t be afraid to use both or approximations if you have a more complicated value such as a price