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.