Too slow when using both 'tax_query' and 'meta_query' both in WP_Query

Question

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 );    

Answer 1

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.

Answer 2

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.