0

I'm trying to search a custom post type of "Properties", while doing so I'm also searching the custom fields using multiple (anywhere from 5-30) meta_queries. The problem is Wordpress or MySQL is throwing an error saying I hit the limit of the amount of JOIN statements that can be used.

The error: [The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay]

Since I'm using WP_Query is it even possible to set those options? Any other ways I can get around this?

Here are an example of my query $args:

Here is my $args that I'm passing to WP_Query:

 Array
(
[numberposts] => -1
[post_type] => properties
[status] => publish
[tax_query] => Array
    (
        [relation] => AND
        [0] => Array
            (
                [taxonomy] => markets
                [field] => id
                [terms] => Array
                    (
                        [0] => 27
                        [1] => 157
                    )

            )

    )

[meta_query] => Array
    (
        [relation] => AND
        [0] => Array
            (
                [key] => sleeps
                [value] => 3
                [type] => NUMERIC
                [compare] => >=
            )

        [1] => Array
            (
                [key] => minimum_rate_0_rate
                [value] => Array
                    (
                        [0] => 999
                        [1] => 2750
                    )

                [type] => NUMERIC
                [compare] => BETWEEN
            )

        [2] => Array
            (
                [key] => management
                [value] => Array
                    (
                        [0] => Professionally Managed
                        [1] => Privately Owned
                    )

                [type] => CHAR
                [compare] => LIKE
            )

        [3] => Array
            (
                [key] => property_type
                [value] => Array
                    (
                        [0] => Home
                        [1] => Condo
                    )

                [type] => CHAR
                [compare] => LIKE
            )

        [4] => Array
            (
                [key] => number_of_bedrooms
                [value] => Array
                    (
                        [0] => 1
                        [1] => 15
                    )

                [type] => NUMERIC
                [compare] => BETWEEN
            )

        [5] => Array
            (
                [key] => total_number_of_baths
                [value] => Array
                    (
                        [0] => 1
                        [1] => 10
                    )

                [type] => NUMERIC
                [compare] => BETWEEN
            )

        [6] => Array
            (
                [key] => pet_policy
                [value] => cat dog
                [type] => CHAR
                [compare] => LIKE
            )

        [7] => Array
            (
                [key] => pools_hot_tubs_and_saunas
                [value] => Array
                    (
                        [0] => Hot Tub
                    )

                [type] => CHAR
                [compare] => LIKE
            )

        [8] => Array
            (
                [key] => appliances_and_furniture
                [value] => Washer
                [type] => CHAR
                [compare] => LIKE
            )

        [9] => Array
            (
                [key] => phone_and_internet
                [value] => Internet
                [type] => CHAR
                [compare] => LIKE
            )

    )

)

  • Maybe you could separate the big query in several smaller ones... – Gioia Fueter Jun 26 '14 at 14:04
  • I'm guessing I'd have to move away from using WP_Query then? How would you suggest I go about it then? – paper_robots Jun 26 '14 at 14:08
  • Mhhh, difficult to say without more info...Could you put a concrete example? – Gioia Fueter Jun 26 '14 at 14:10
  • Added a query example to the top. That is a pretty mild one, but that's right about when it starts to break. – paper_robots Jun 26 '14 at 14:13
  • 1
    This answer might help: stackoverflow.com/questions/966815/mysql-max-join-size-errors – Gioia Fueter Jun 26 '14 at 16:26
1

This seemed to do the trick, running it before my WP_Query:

$wpdb->query('SET OPTION SQL_BIG_SELECTS = 1');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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