Set SQL_BIG_SELECTS and MAX_JOIN_SIZE on a WP_Query

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

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' );