Clever Geek Handbook

MySQL: "SELECT will check for more than MAX_JOIN_SIZE rows"

I am using PHP and MySQL. My program has a select query that includes joins. When I run it on localhost it works fine, but when I upload it to my server and try to execute it, 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 fix this?

+10


source to share


6 answers


When using PHP, SQL_BIG_SELECTS = 1 must 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>';
}
+17


source


Try running the query prior to your choice:

SET SQL_BIG_SELECTS=1


Is this really done on a huge dataset? Unless it should be solved in another way.

+9


source


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

You must filter the related records more strictly (so there are fewer records in each part of the query). If possible, start with a table where you can filter out most of the records with a simple WHERE clause.

+3


source


I faced the same problem. His drupal site is not so surprised he crashed.

This was an old style request i.e. Select blah From table1, table2, table3 Where table1.id=table2.id And table2.some = 'thing'

Like @VolkerK, the solution was to move the where clauses filtered by the results of table2 to what is matched against table1 in table2 (in fact, join clauses), thus reducing the number of records that must match in table2 with table1 ...

0


source


For me, the solution was to add an index key to all the columns that I used to match.

0


source


If you are using a PDO driver, set PDO::MYSQL_ATTR_INIT_COMMAND

in your array driver_options when creating a new database descriptor

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


source






More articles:


All Articles