2

I recently developed a website for a customer who is a estate agent. Included in the project is a post type called listing. This post type is used for all of their listings and additional information is stored in the postmeta table through the use of Advanced Custom Fields Pro.

On the front-end of the site I've developed a filtering bar where users can select through several filters and submit the form, which in turns generates a complex WP_Query to return the listings that match. Simple enough! While most searches work perfectly fine, some more complex ones will generate an SQL query that hogs the thread. When the database needs to flush everything piles up and the server essentially freezes until someone kills the process. This happens on all environments, not only in production.

For instance, here is a query that checks the interval of price, bedrooms, bathrooms, that the listing includes furniture and which location (post_id) and a post title of "1". Doesn't sound like a super advanced search, does it? (this is the generated SQL)

SELECT DISTINCT   4x5LbPZ_posts.* FROM 4x5LbPZ_posts  LEFT JOIN 4x5LbPZ_term_relationships ON (4x5LbPZ_posts.ID = 4x5LbPZ_term_relationships.object_id) INNER JOIN 4x5LbPZ_postmeta ON ( 4x5LbPZ_posts.ID = 4x5LbPZ_postmeta.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt1 ON ( 4x5LbPZ_posts.ID = mt1.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt2 ON ( 4x5LbPZ_posts.ID = mt2.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt3 ON ( 4x5LbPZ_posts.ID = mt3.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt4 ON ( 4x5LbPZ_posts.ID = mt4.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt5 ON ( 4x5LbPZ_posts.ID = mt5.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt6 ON ( 4x5LbPZ_posts.ID = mt6.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt7 ON ( 4x5LbPZ_posts.ID = mt7.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt8 ON ( 4x5LbPZ_posts.ID = mt8.post_id )  INNER JOIN 4x5LbPZ_postmeta AS mt9 ON ( 4x5LbPZ_posts.ID = mt9.post_id ) LEFT JOIN 4x5LbPZ_term_relationships AS trel ON (4x5LbPZ_posts.ID = trel.object_id) LEFT JOIN 4x5LbPZ_term_taxonomy AS ttax ON (  ( ttax.taxonomy = 'category' )  AND trel.term_taxonomy_id = ttax.term_taxonomy_id) LEFT JOIN 4x5LbPZ_terms AS tter ON (ttax.term_id = tter.term_id)  LEFT JOIN 4x5LbPZ_postmeta AS m ON (4x5LbPZ_posts.ID = m.post_id)  WHERE 1=1 AND ( ( (
  4x5LbPZ_term_relationships.term_taxonomy_id IN (3)
) AND (((((4x5LbPZ_posts.post_title LIKE '%1%') OR (4x5LbPZ_posts.post_content LIKE '%1%'))) OR ((tter.slug LIKE '%1%'))  OR ((ttax.description LIKE '%1%'))  OR ((m.meta_value LIKE '%1%')) ))  AND (
  ( 4x5LbPZ_postmeta.meta_key = 'listing_status' AND 4x5LbPZ_postmeta.meta_value NOT IN ('pending') )
  AND
  ( mt1.meta_key = 'listing_status' AND mt1.meta_value IN ('for_sale') )
  AND
  ( mt2.meta_key = 'listing_price' AND CAST(mt2.meta_value AS SIGNED) >= '0' )
  AND
  ( mt3.meta_key = 'listing_price' AND CAST(mt3.meta_value AS SIGNED) <= '7500000' )
  AND
  ( mt4.meta_key = 'listing_bedrooms' AND CAST(mt4.meta_value AS SIGNED) >= '0' )
  AND
  ( mt5.meta_key = 'listing_bedrooms' AND CAST(mt5.meta_value AS SIGNED) <= '36' )
  AND
  ( mt6.meta_key = 'listing_bathrooms' AND CAST(mt6.meta_value AS SIGNED) >= '0' )
  AND
  ( mt7.meta_key = 'listing_bathrooms' AND CAST(mt7.meta_value AS SIGNED) <= '7' )
  AND
  ( mt8.meta_key = 'listing_furniture' AND mt8.meta_value = 'true' )
  AND
  ( mt9.meta_key = 'listing_location' AND mt9.meta_value = '80' )
) AND 4x5LbPZ_posts.post_type = 'listing' AND (4x5LbPZ_posts.post_status = 'publish' OR 4x5LbPZ_posts.post_status = 'acf-disabled')) AND post_type != 'revision') AND post_status != 'future' GROUP BY 4x5LbPZ_posts.ID ORDER BY 4x5LbPZ_posts.post_date DESC;

I really only use TYPE, KEY, VALUE and COMPARE in the meta_query array so it's build like it should using WordPress, but yet it becomes unfathomably slow. When this happens the requests will build up until the entire site becomes unresponsive.

6
  • 1
    Your query contains 15 JOINs. Thats alot. Have you thought of constructing your own, optimized queries, instead of reyling on WordPress?
    – kero
    Aug 28, 2018 at 11:55
  • Of course. It's just that this is a project that was delivered months ago and this started occurring just recently. I had hoped there was some easy fix that could help optimise the tables or the queries that WP_Query spits out.
    – Jonathan
    Aug 28, 2018 at 12:06
  • Also, the problem occurs when there are more aliases and more keys and values to compare. If I get rid of 2-3 of them the query works as intended. Shouldn't WP_Query be able to produce better queries seeing as it's the recommended way to query post types and their data?
    – Jonathan
    Aug 28, 2018 at 12:08
  • 1
    In my experience, WP_Query is quite bad if your query gets a little more complex
    – kero
    Aug 28, 2018 at 12:14
  • 2
    Querying by postmeta has always been slow. It would be wise to consider creating custom taxonomies instead - tax queries are much more performant. In the meantime, you may want to try a database optimization plugin to clean out revisions, orphaned postmeta, etc., but that's only a bandaid.
    – WebElaine
    Aug 28, 2018 at 13:33

1 Answer 1

2

There are several options:

  • Use some Lucene-based search index and cache your queries there. Power it with lots of RAM so nothing hits your DB after the first query. Use something like…
    • Elasticsearch
    • Solr
    • Sphinx
    • Do not attempt to run Lucene plain … you will find yourself in hell. Nobody does that for a good reason.
  • Rewrite your Query and use a raw query instead of \WP_Query.
  • Split your query into different queries. Cache the result in a key/value storage (db-server).
    • Use Memcached (meh)…
    • or Redis (better, as you can cluster it and it configures better with load balanced installations.)
  • The easiest way out: Write an update script that loops over your 300 objects/estates. 300 is not much. Convert it to a taxonomy. Ease your life in the long run.

Recommendations:

  • Log slow queries.
  • Add monitoring to your stack.
  • Alert yourself when things go off. Fix it, before your client knows. Inform them about what you fixed. Have happy clients for a long time. Find out that CLV is underrated.
1
  • Thank for you this detailed answer. Seems like I will have to go through all the information and see what can be broken out in custom taxonomies instead. It will be a major job for sure :/
    – Jonathan
    Sep 13, 2018 at 6:55

Your Answer

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

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