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

I used a very sinmple wp_query to retrive 10 posts on site with more then 100k posts.

This query run on wp-multi site, same query against small site in same network (same db server) took very few milisec (0.032888 | 0.021731 | 0.020796 | 0.02127)

Main issue is when try to run it on huge site (+100k posts).

The wp_query dump is:

print_r($args);

// output
Array
(
    [post_type] => post
    [posts_per_page] => 10
    [paged] => 
    [orderby] => date
    [order] => DESC
    [meta_query] => Array
        (
            [0] => Array
                (
                    [key] => city
                    [value] => Las Vegas
                    [compare] => =
                )

        )

    [cat] => 2
)

dump of $wp_query->request:

SELECT SQL_CALC_FOUND_ROWS wpui_2_posts.id 
FROM   wpui_2_posts 
       LEFT JOIN wpui_2_term_relationships 
              ON ( wpui_2_posts.id = wpui_2_term_relationships.object_id ) 
       INNER JOIN wpui_2_postmeta 
               ON ( wpui_2_posts.id = wpui_2_postmeta.post_id ) 
WHERE  1 = 1 
       AND ( wpui_2_term_relationships.term_taxonomy_id IN ( 2 ) ) 
       AND (( wpui_2_postmeta.meta_key = 'city' 
              AND wpui_2_postmeta.meta_value="las vegas" )) 
       AND wpui_2_posts.post_type="post" 
       AND ( wpui_2_posts.post_status="publish" 
              OR wpui_2_posts.post_status="private" ) 
GROUP  BY wpui_2_posts.id 
ORDER  BY wpui_2_posts.post_date DESC 
LIMIT  0, 10 

Explain query from MySQL:

ID  select_type table   type    possible_key    key key_len ref rows    Extra
1   SIMPLE  wpui_2_term_relationships   ref PRIMARY,term_taxonomy_id    term_taxonomy_id    8   const   59097   Using index; Using temporary; Using filesort
1   SIMPLE  wpui_2_posts    eq_ref  PRIMARY,type_status_date    PRIMARY 8   wpdb.wpui_2_term_relationships.object_id   1   Using where
1   SIMPLE  wpui_2_postmeta ref post_id,meta_key    post_id 8   wpdb.wpui_2_term_relationships.object_id   3   Using where

this query took very large time (5.715333 | 6.295236 | 5.110536 | 5.138607 | 5.164155)

Current amount of rows

• wpui_2_posts 209548
• wpui_2_term_relationships 118156
• wpui_2_postmeta 2087567

Any suggestion is really appreciated

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