4

I have added the full array in end of the post. I have passed 'post_type', 'posts_per_page', 'tax_query' and 'meta_query' for WP_Query.

This page take around 10 - 15 seconds to load... It is too much :-(... [Sorry : 10-15 seconds is wrong, when calculating it is about a minute... ]

So I was looking at why it is too slow....

If I remove tax_query part page load within 2 seconds... If I remove meta_query part again page load within 2 seconds.

But when I use both tax_query and meta_query page takes more than 10 seconds.... What is the reason for it? How can I do to make this speed up?

According to comments and https://tomjn.com/2016/12/05/post-meta-abuse/, meta queries are too slow.

Then question is, If I remove tax_query part page load within 2 seconds... If I remove meta_query part again page load within 2 seconds.

But when I use both tax_query and meta_query page takes too much time.... What is the reason for it? How can I do to make this speed up?

$args = array(
                'post_type' => 'movie',
                'posts_per_page'=>-1,
                'tax_query' => array(
                        array(
                            'taxonomy' => 'movie-type',
                            'field'    => 'id',
                            'terms'    => $movie_type,
                        ),
                        array(
                            'taxonomy' => 'genre',
                            'field'    => 'id',
                            'terms'    => $genre,
                        ),
                        array(
                            'taxonomy' => 'distributor',
                            'field'    => 'id',
                            'terms'    => $distributor,
                        ),
                        array(
                            'taxonomy' => 'orgin',
                            'field'    => 'id',
                            'terms'    => $orgin,
                        ),
                        array(
                            'taxonomy' => 'stage',
                            'field'    => 'id',
                            'terms'    => $stage,
                        ),
                    ),

                'meta_query' => array(                          
                                    array(
                                        'key'     => 'wpcf-episode',
                                        'value'   => $episode,
                                        'type'    => 'numeric',
                                        'compare' => 'IN',
                                    ),
                                    array(
                                        'key'     => 'wpcf-interval-period',
                                        'value'   => $interval,
                                        'type'    => 'numeric',
                                        'compare' => 'IN',
                                    ),
                                    array(
                                        'key'     => 'wpcf-domestic-total-gross-usd',
                                        'value'   => array( $domestic_total_gross_usd_s, $domestic_total_gross_usd_f ),
                                        'type'    => 'numeric',
                                        'compare' => 'BETWEEN',
                                    ),
                                    array(
                                        'key'     => 'wpcf-domestic-opening-usd',
                                        'value'   => array( $domestic_opening_usd_s, $domestic_opening_usd_f ),
                                        'type'    => 'numeric',
                                        'compare' => 'BETWEEN',
                                    ), 
                                    array(
                                        'key'     => 'wpcf-international-total-gross-usd',
                                        'value'   => array(0,1000000000),
                                        'type'    => 'numeric',
                                        'compare' => 'BETWEEN',
                                    ), 
                            ),

            );

            $all_movies = new WP_Query( $args );    
8
  • 1
    Meta queries are slow, very slow. See tomjn.com/2016/12/05/post-meta-abuse If it's not too late to change the data to avoid meta data and use taxonomy instead, do it. Also, because of the complexity involved, even tax query would be too slow if you convert all meta to taxonomies (assuming you could do that). For such complexity, your only option is to use something like Elastic Search or Solr to run queries.
    – gmazzap
    Feb 14, 2018 at 10:59
  • @gmazzap Thanks. Is it possible to use something like international total gross of movies for taxonomy ? I mean if there are 1000 movies of the system, there will be created 1000 taxonomy names since it is almost unique the total gross for each each movie. I have read that article and again I will read it very carfully... But I have a question... If I remove tax_query part page load within 2 seconds... If I remove meta_query part again page load within 2 seconds. But when I use both tax_query and meta_query page takes more than 10 seconds.... What is the reason for it? Feb 14, 2018 at 11:12
  • No, you're right, taxonomies really wouldn't work for numerical data like that. For one thing you can't even do BETWEEN queries on their names, so you wouldn't be able to use them in a query like this, plus it would be weird having a term for every numerical value. An alternative to Elastic Search or similar (though that's a good idea) would be to create a table for your numerical data, like what's in your meta queries. If you have those as columns in one table that'll dramatically reduce the number of joins required, but will require work to integrate with WP_Query. Feb 14, 2018 at 11:18
  • @JacobPeattie Good idea... Thanks.. But again some additional works since I use some plugins to import and export... Custom table mean I may have to write the export and import also... And still I am not clear about the last part of my question ( If I remove tax_query part page load within 2 seconds... If I remove meta_query part again page load within 2 seconds. But when I use both tax_query and meta_query page takes more than 10 seconds.... What is the reason for it?) Feb 14, 2018 at 11:32
  • 1
    Yes you're right that it would be a lot of work. A hosted solution like ElasticPress or Algolia might be the most economical in terms of time spent, but can be pricey. Regarding performance, I suspect it's just that the performance impact is exponential, so the cost of having both isn't just 2x but 5x since each extra bit gets slower and slower, but I'm not that much of an expert. I'd be curious to see what happens if you have half the tax queries and half the meta queries. See if that also drops to 2s. Feb 14, 2018 at 11:40

2 Answers 2

18

This is a stupendously expensive query, you can mitigate but you can't eliminate the performance issues.

So lets start with the low hanging fruit and work our way up to the big problems

posts_per_page

Always set a maximum, even if it's one you never expect to reach. -1 is asking for trouble:

  • There's no upper limit, so the DB keeps working even after you have what you wanted
  • There's no upper limit, so it's a very real possibility you'll get more results than you can store in memory, leading to memory exhaustion
  • There's no upper limit, so there may be so many results that simply transferring them from the database may take too much time
  • There's no upper limit, so you may run out of execution time trying to display them all
  • There's no upper limit, so the DB can't factor in optimisations to save memory

All the while, other people are using the site at the same time. This is a recipe for high CPU and memory usage (especially when combined with the later problems)

So set it to 50 or 100, and use pagination

Post status

You don't set the post status, which most people don't, but if you did WP_Query would be able to optimise the query to better use table indexes! Always explicitly set the post status you want for performance reasons.

This is because the default is not just publish, but it also factors in private posts that only you can see

pre_get_posts

If I'm not mistaken this looks like search functionality for finding and filtering movies. Why is this a custom query on a page template? Just think of the time spent on that main query that's been discarded, time wasted, resources spent then thrown away

me: I once asked my assistant for a hot chocolate but then changed my mind. Rather than say so, I waited until they came back from the coffee shop with the hot chocolate to tell them I actually wanted a tea, sending them out a second time.

Now my tea is 30 minutes late and I had to throw away a hot chocolate, and people are complaining I'm slow

you: Couldn't you have mentioned it before they went instead of waiting till the end to tell them?

By using pre_get_posts you can simplify your loop while eliminating an entire query. I strongly recommend you ask this as a new question:

How do I use pre_get_posts on a page template to avoid a second query?

Multi-dimensional Taxonomy Query

Taxonomies are faster than post meta, much faster, but they are not free.

A single taxonomy query can be quick, but multiple in the same query can drag things down

If you can reduce or combine these in any way, the query will get faster. Intermediate values derived from existing ones can be used for performance purposes by calculating in advance.

Multi-dimensional Post Meta Queries

I cannot overstate how awful the performance of post meta queries are. They are in the top 3 of things to do to slow down your site.

I suspect that the poor performance you're getting from these is actually on the optimistic end, and that as the site gets used, performance will actually get worse as the post meta table expands.

Avoid post meta queries at all costs

But to have multiple post meta queries, is even slower.

For this I'll need several sub-sections:

It's Multiplication not Addition

If I remove tax_query part page load within 2 seconds... If I remove meta_query part again page load within 2 seconds.

But when I use both tax_query and meta_query page takes more than 10 seconds

Because the cost is not additive, speed != taxspeed + metaspeed, it's more like speed = taxspeed * metaspeed. Not just that, each dimension of the query has its own properties that can multiply it further. The end result is that adding 1 more dimension to the query scales the query logarithmically, not linearly.

In addition, you're now involving an additional set of tables for MySQL to search across. Twice as many tables to search, twice as many indexes to load, twice as many temporary tables ( potentially a lot more than twice )

Table Scans

Lets consider these particular meta queries:

array(
    'key'     => 'wpcf-international-total-gross-usd',
    'value'   => array(0,1000000000),
    'type'    => 'numeric',
    'compare' => 'BETWEEN',
), 

These can't rely on an index as there's math that needs to take place to determine if it fits or doesn't fit. As a result, MySQL now needs to do a table scan to build a temporary table. It has to do this for each of these, and table scans are slooow, and temporary tables take up memory. Then it has to do the original query but using these new temporary tables, adding more tables to the mix. Then it has to clean up these tables.

You might even have ran out of physical memory during this process, pushing things on to the HD in swap memory, slowing down the entire process. Lets say that it only takes up half of memory in the worst scenario, that's still a maximum of 2 queries at a time before the site starts to suffer

Fixing The Query

Here is what I'm prescribing:

  • reduce the number of options to search by, or use Elastic Search
  • eliminate the post meta queries entirely
  • store some post meta as taxonomies
  • Store intermediate terms for post meta as buckets

reduce the number of options to search by, or use Elastic Search

You're simply querying for too many things. MySQL isn't built for these kinds of things! So either reduce the number of things you're querying for, precalculate groups so you can combine them, or use elastic search

ES is okayish for simply queries, but it really shines when these kinds of queries crop up. As a bonus your site search becomes significantly better. You won't find ES on shared hosting, but you will on some managed hosting. You can also set it up yourself on VPS'

Eliminate the post meta queries entirely

If it's a choice between taxquery and metaquery, taxquery always wins, hands down. It has better performance, better memory management, faster queries, etc

store some post meta as taxonomies

These meta queries should be tax queries:

array(
    'key'     => 'wpcf-episode',
    'value'   => $episode,
    'type'    => 'numeric',
    'compare' => 'IN',
),
array(
    'key'     => 'wpcf-interval-period',
    'value'   => $interval,
    'type'    => 'numeric',
    'compare' => 'IN',
),

Storing movie episode and interval period as post meta is inefficient now we know that they're searchable/filterable. They should be stored as a taxonomy

What's more, this calculation could have been done when the movie post was saved, and stored as a taxonomy term/tag/category:

array(
    'key'     => 'wpcf-international-total-gross-usd',
    'value'   => array(0,1000000000),
    'type'    => 'numeric',
    'compare' => 'BETWEEN',
),

It's basically asking did the movie get between 0 and 1 billion gross USD, which is pretty much every movie. With this in mind, is it really necessary? Could this field simply be eliminated?

Store intermediate terms for post meta as buckets

I'm looking at these:

array(
    'key'     => 'wpcf-domestic-total-gross-usd',
    'value'   => array( $domestic_total_gross_usd_s, $domestic_total_gross_usd_f ),
    'type'    => 'numeric',
    'compare' => 'BETWEEN',
),
array(
    'key'     => 'wpcf-domestic-opening-usd',
    'value'   => array( $domestic_opening_usd_s, $domestic_opening_usd_f ),
    'type'    => 'numeric',
    'compare' => 'BETWEEN',
), 

And I can imagine that there is a set of input boxes, that the user can type numbers into. My inner DB performance guru squirms in horror, but why?

Most things in programming have a tradeoff, even if you don't realise it. In this case, you've ran into a performance vs accuracy tradeoff, and have chosen accuracy. So the solution here is to compromise.

Keep these values stored as post meta, but don't query on them. Instead, query on calculated values, specifically, taxonomy terms representing buckets.

A lot of people do this, if you look at e-commerce sites they don't give you input boxes, they give you ranges to choose from. $500-$1000, $1000-$2000, etc ranges of memory capacity, and so on. This simplifies the UI and improves UX, but importantly, it dramatically improves the performance of filters.

So, create 2 new taxonomies, domestic-opening and domestic-total-gross, create buckets to put your movie posts into, and then present those as filter options. Now you have eliminated post meta queries.

As a bonus, you now have taxonomy-domestic-opening.php and a free archive, as well as better REST support. Use some hooks on save_post to make sure these terms get set using the post meta value to set them, and all will be good

I'm already nearly done I don't have time to go back and redo all my content

And you don't have to! Use the power of WP CLI to convert your existing content so you don't have to recreate it.

If you're on shared hosting and can't run WP CLI commands, download your site to a local environment, such as VVV, then run the WP CLI command on your own computer and upload the result back to your host.

3
  • 3
    I learned thousands of things... I have no words to appreciate this... Thanks so much... I will again read this tomorrow... Feb 15, 2018 at 12:51
  • Telling people to use taxonomies instead of Meta_queries is like telling someone to drive a car with blocks instead of wheels. While the amount of effort put into this answer is admirable, the answer itself is horrible. Meta Data is designed to be queried against. Not using it as intended, or overusing taxonomies in lieu of them is just terrible advice. Sorry, but this gets a downvote from me. Aug 31, 2018 at 5:25
  • 1
    If that was the case, categories and tags would be stored in meta. Post meta tables are designed for finding key value pairs when you already know the post ID. What the OP is doing is the reverse, trying to find the post ID when they already know the key/value, and that's what's expensive, and that's the whole point of the taxonomy tables. It appears instead you've conflated get_post_meta type queries, and WP_Query( [ 'meta_query' => type queries, this isn't a blanket never use post meta thing, it's a don't search for posts via their meta thing
    – Tom J Nowell
    Aug 31, 2018 at 16:09
0

Modern versions of MySQL and MariaDB make it much easier to index wp_postmeta and the other metadata tables to handle meta queries efficiently. Here's a plugin to do that.

The new database versions make it possible to stop using prefix indexes (like meta_key (meta_key(191)) on the columns in the meta tables, and index them completely.

That, in turn, makes it possible to exploit the clustered primary key data structures in the new versions.

This SQL statement does, to wp_postmeta, what the plugin does.

ALTER TABLE wp_postmeta 
   ADD UNIQUE KEY meta_id (meta_id), 
   DROP PRIMARY KEY,  ADD PRIMARY KEY (post_id, meta_key, meta_id),
   DROP KEY meta_key, ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id), 
   ADD KEY meta_value (meta_value(32), meta_id),
   DROP KEY post_id;

Be careful, if you drop the primary key, to do two things:

  • ADD a UNIQUE key on meta_id first.
  • DROP the old primary key and ADD the new one in a single SQL statement.

See also this Github issue and this writeup of the situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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