2

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

  • 2
    For a query like this you’d probably get better performance if city was a taxonomy and you looked up by ID. – Jacob Peattie Dec 15 '17 at 15:15
  • Meta queries are super, super slow – Tom J Nowell Dec 15 '17 at 15:42
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.