Nested meta query causes mySQL timeout, what are some workarounds?

  • Hi there,

    I’m having a challenging issue with a complex meta query so I’m looking for suggestions from you fine people as to the best way to approach this.

    We have a post type “property” and each property has a value (price). However the properties are all over the world and their respective values are listed in different currencies.

    So we have two pieces of post meta for each property:
    1. property_currency
    2. property_value

    Now what I want to do is find all the properties between a given search range in the visitors currency eg. Everything between $100,000USD and $500,000USD if the visitor was from the USA. Now I can do the math and convert that to all the other currencies to get the appropriate search range for each currency. What I’m struggling with is the meta query. It is breaking the server and causing MySQL to timeout.

    Here is an simplified version of the query… The most simple version which still breaks MySQL.

            $args = array(
            'post_type' => 'property',
            'showposts' => 20,
            'meta_query' => array(
                                'relation' => 'OR',
                                array (
                                    'relation' => 'AND',
                                    array (
                                        'key' => 'property_currency',
                                        'value' => 'AUD',
                                        'compare' => 'LIKE'
                                    ),
                                    array (
                                        'key' => 'property_value',
                                        'value' => array( '145000', '725000' ),
                                        'type' => 'numeric',
                                        'compare' => 'BETWEEN'
                                    ),
                                ),
                                array ( 
                                    'relation' => 'AND',
                                    array (
                                        'key' => 'property_currency',
                                        'value' => 'USD',
                                        'compare' => 'LIKE'
                                    ),
                                    array (
                                        'key' => 'property_value',
                                        'value' => array( '100000', '500000' ),
                                        'type' => 'numeric',
                                        'compare' => 'BETWEEN'
                                    ),
                                )
                        )
        );

    Note: in reality there are many more currencies, I have just narrowed it down to two for example.

    Yes I know I could store all property values in USD and run a cron each night to update the database values with the current exchange rates or something like that. However as the properties are added by 3rd parties in their local currency I would rather just store the native currency value for each property in the database. It should not be that complex to query them all… but alas it is.

    My googling of this issue has suggested running multiple queries and just returning ID’s then doing an array merge, however that would be A LOT of consecutive queries when we are talking 20+ currencies. Surely there is a better way…

    Over to you ??

    Thanks very much!

    • This topic was modified 4 years, 11 months ago by nicole2292.
    • This topic was modified 4 years, 11 months ago by nicole2292.
Viewing 3 replies - 1 through 3 (of 3 total)

  • I can’t think of a bettre way. You either do the calculations when you do the query (which means slow queries, and you’ve seen) or you store a base price and convert that to match whatever conversion rates there are (more work, but faster queries). I would personally store the base price and query that as it will be infinately less strain on the server resources.

    If you really want to use the route that you’re on now, I’d realyl suggest hand-writing a bit if SQL for the query instead of relying on the WordPress functions. Yes, they do work, but they do add a lot of overhead and don’t let you do things quite as nicely as you would if you did it yourself. You’ll probably find a better way to go about it doing that then trying to masasge the WordPress query system like that.

    Thread Starter nicole2292

    (@nicole2292)

    Thanks very much for your reply and suggestions. Hand writing SQL is not my strong suit… time to get my hands dirty and improve in that area I suspect ??

    Moderator bcworkz

    (@bcworkz)

    You can use the SQL that WP comes up with in $WP_Query::request as a starting point and try optimizing it. The real problem is complex meta queries are very inefficient. It’d be much faster to organize important values in a custom table that is joined to the posts table by ID. Then the table can be organized and indexed properly for optimal queries. Of course that would mean a major data overhaul. While that sounds ominous, it needn’t be too awful if a one time script is written to manage the conversion.

    While doing multiple queries will solve the time out issue, it doesn’t solve the overall server load issue.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Nested meta query causes mySQL timeout, what are some workarounds?’ is closed to new replies.