Too slow when using both ‘tax_query’ and ‘meta_query’ both in WP_Query

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.

Related Posts:

  1. How can I save an array from a random post sequence for later use?
  2. Include current post into loop
  3. Loop through array of pages
  4. Repeat array inside array through while loop
  5. Iterate through ID’s in loop
  6. Issue with WP_Query (need a array of selected ID’s)
  7. Get post count of current loop when using multiple queries on one page
  8. Display products from specific category in shop page
  9. get_template_part in for loop
  10. What kind of object type is WP_Query?
  11. “pre_get_posts” firing on every query
  12. Getting an array out of WPQuery
  13. Show two random posts from custom post type
  14. Add the “active” class only to the first loop item in a WordPress query [closed]
  15. Merging a complex query with post_rewind and splitting posts into two columns
  16. Use Transient API to cache queries for all posts in all categories?
  17. Loop inside the loop
  18. Pagination Not working on Home Page with 2 Query
  19. meta_query not working properly
  20. cloning a WP_Query
  21. Placing a div or img in between a post array using WP Query [closed]
  22. How to query ‘posts_per_page’ to display a different blog posts index template?
  23. Sort by meta key on archive page
  24. How is WP_Query connected to WP_Post in The Loop?
  25. Query sticky posts with thumbnails
  26. Show all parents and children in custom post type in right order
  27. Why is this coming back as null? Thats wrong. There is one post
  28. Ignore post by meta value in the main query
  29. How can I pick a single post from the latest 3?
  30. Get posts for last working week in WP_Query
  31. query user display_name failed in a custom mysql query foreach
  32. Query posts by specific word on title
  33. Random loop with code to prevent duplicate output returns no output at all from time to time
  34. Multiple taxonomy And acf filter group by
  35. How to use offset in WP_Query
  36. Reset postdata to custom query in nested queries
  37. How to Filter Posts by Custom Fields?
  38. Loop Split two Columns
  39. How to list posts with disabled/closed comments, with pagination?
  40. How to make a second query offset -2 from current post
  41. Custom taxonomy.php not working
  42. different for loop if screen resolution < 1000px
  43. Best choice for multiple loop in page?
  44. WP Query – order posts by meta field first and then order the rest
  45. Wp_query with 2 meta keys and array of meta values
  46. sort and display posts by custom field (wp-query and the loop))
  47. How to create custom query by keyword in post title?
  48. Create a loop on my pages with new “WP_Query”
  49. WP ForLoop to compare meta information of posts to determine what post to display
  50. How to filter query loop block with a search string from the query parameters
  51. inserting content of 1 Post to in another with a template hierarchy
  52. Get all posts as an array ID => Name
  53. How can I comment comma-separated array values?
  54. Different Loop for tag pages?
  55. Related Posts Excluding Certain Categories
  56. How to access $wp_query object from array of posts given by get_posts?
  57. Display all attached image of every post of custom post type and link to original post
  58. 2 queries with counters
  59. Multiple meta query from array
  60. Writing less unnecessary code with WordPress
  61. How to avoid filling up an array each time I run a WP_query?
  62. Search.php – return number of results but cannot loop through
  63. Creating ‘posts page’ loop based on the page itself
  64. WordPress Queries Not Working?
  65. Filter subpages in while loop from WP Query object
  66. Use have_posts() with array of post results retrieved by $wpdb->get_results
  67. Custom query does not find pages
  68. How to sort by most watched in X days/months
  69. How to use custom page for all posts with custom url, call another directory?
  70. Combine relationship posts with existing wp_query
  71. Custom wp_query inside a conditional stament inside a template part doesn’t work: why?
  72. Duplice post with standard WP loop – fixed by using query_posts() instead
  73. WP_Query breaking the loop in a nested loop
  74. Check the stored / cached WP_Query with transients on post change
  75. New template file does not load category-specific post
  76. Related posts queries
  77. What code to use in an array to call the current sub-category?
  78. Get categories within specific term
  79. Custom query for custom post type not getting correct post ID
  80. Query post with content only
  81. update_post_meta() not updating
  82. Why is my query not giving results?
  83. Continue loop after $queryObject
  84. active link for most recent post on vertical tabs
  85. how to query for meta_value have array
  86. WP_Query for attachments without duplicating post_parent and displaying tagged image
  87. List ALL posts within last week from current category on category page
  88. WordPress WP_Query Array Custom search via taxonomies
  89. loop through custom post types with meta data
  90. Dynamically name array in WordPress loop – add/get values
  91. Using Query In Post Type Archives
  92. How ‘secure’ are loops?
  93. Creating Custom Query
  94. Multiple loops on index page with sticky post and pagination
  95. How to do a loop inside a loop?
  96. Main loop querying current template’s info only in custom category archive pages, not my posts
  97. tax_query not working properly with get_posts
  98. the_excerpt() not working in custom archive
  99. How can I display sticky posts at first in wp_query?
  100. Show number of posts AND number on current page (cannot make it work)

Leave a Comment