CODEWITHSUNDEEP
sqlmysqljoin
Asheesh
Asheesh

Reputation:

MySQL: "The SELECT would examine more than MAX_JOIN_SIZE rows"

I am using PHP and MySQL. In my program there is a select query involving joins. When I run it on localhost it's working fine but when I upload it on my server and try to execute it then it generates the following error:

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

How can I correct this?

Upvotes: 16

Views: 44196

Answers (6)

Mcdiamond Ibifa
Mcdiamond Ibifa

Reputation: 1

If you are using PDO driver, set the PDO::MYSQL_ATTR_INIT_COMMAND in your driver_options array when constructing a new database handle

like so:

$dbh = new PDO('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION SQL_BIG_SELECTS=1'));

Upvotes: 0

Tum
Tum

Reputation: 7585

For me the solution was to add an index key to all the columns the joins used to match.

Upvotes: 0

Kevin Marsden
Kevin Marsden

Reputation: 764

When using PHP, SQL_BIG_SELECTS=1 should be set in a separate query before your main query. For example:

$mysqli = new mysqli("localhost", "root", "password", "db"); 

$mysqli->query("SET SQL_BIG_SELECTS=1");  //Set it before your main query

$results = $mysqli->query("SELECT a, b, c FROM test");
while($row = $results->fetch_assoc()){
    echo '<pre>';
    print_r ($row);
    echo '</pre>';
}

Upvotes: 24

Sam
Sam

Reputation: 5536

I've ran into the same problem. Its a drupal site so no surprise that it fell over.

It was an old style query, ie Select blah From table1, table2, table3 Where table1.id=table2.id And table2.some = 'thing'

Like @VolkerK says, the solution was to move the where clauses that filtered table2 results before that which matched table1 to table2 (effectively the join clauses), thus decreasing the amount of records needing to match in table2 to table1.

Upvotes: 0

VolkerK
VolkerK

Reputation: 96159

The parameter's effect is documented at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_join_size.

You should filter the involved records more strictly (so there are less records involved in each part of the query). If possible start with the table where you can filter out the most records with a simple WHERE-clause.

Upvotes: 3

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340356

Try running as a query previous executing your select:

SET SQL_BIG_SELECTS=1

Is this really executing over a huge dataset? If not this should be solved in a different way.

Upvotes: 14

Related Questions