Despite using;

$mysqli = new mysqli("localhost", "user", "pass", "db"); 
$mysqli->query('SET SQL_BIG_SELECTS = 1');
$mysqli->query('SET MAX_JOIN_SIZE = 999');

$the_query = new WP_Query( $args ); 

I'm receiving;

[23-Apr-2018 14:37:31 UTC] WordPress database 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 for query... WP_Query->__construct, WP_Query->query, WP_Query->get_posts

The original query works, but stopped once I added another meta query that exceeded the cap despite that additional meta query working when I comment out another.

How can I get the $mysqli query settings to apply to $the_query?

Despite using;

$mysqli = new mysqli("localhost", "user", "pass", "db"); 
$mysqli->query('SET SQL_BIG_SELECTS = 1');
$mysqli->query('SET MAX_JOIN_SIZE = 999');

$the_query = new WP_Query( $args ); 

I'm receiving;

[23-Apr-2018 14:37:31 UTC] WordPress database 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 for query... WP_Query->__construct, WP_Query->query, WP_Query->get_posts

The original query works, but stopped once I added another meta query that exceeded the cap despite that additional meta query working when I comment out another.

How can I get the $mysqli query settings to apply to $the_query?

• database
• query

Share Improve this question edited Apr 23, 2018 at 19:59 Clemens Tolboom 35311 silver badge1111 bronze badges asked Apr 23, 2018 at 14:49 Aus-tnAus-tn 711 silver badge55 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

According to WP_Query hit max joins... How else can I build a search function that uses custom fields? you should use

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

which makes sense as you set it for the current connection. You create a separate connection through new mysqli and WordPress has another on $wpdb.

The $wpdb->query lines were being ignored, due to the restrictions on my host. I have a VPS through BlueHost, and support informed me that they will not comply with any requests to adjust these values. I was also unable to set these configurations via phpMyAdmin due to user restrictions (despite being logged in as root)

For anyone still looking for an answer to this, you can set SQL_BIG_SELECTS to 1 for all your queries by adding the following code to your functions.php file:

function enable_big_selects_for_queries() {
    global $wpdb;
    $wpdb->query( 'SET SQL_BIG_SELECTS=1' );
}
add_action( 'init', 'enable_big_selects_for_queries' );