MySQL：“SELECT 将检查超过 MAX_JOIN_SIZE 行”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/943423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: "The SELECT would examine more than MAX_JOIN_SIZE rows"
提问by Kevin Marsden
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:
我正在使用 PHP 和 MySQL。在我的程序中有一个涉及连接的选择查询。当我在本地主机上运行它时它工作正常但是当我将它上传到我的服务器并尝试执行它时它会产生以下错误：
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
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?
我该如何纠正？
回答by Kevin Marsden
When using PHP, SQL_BIG_SELECTS=1 should be set in a separate query before your main query. For example:
使用 PHP 时，SQL_BIG_SELECTS=1 应在主查询之前的单独查询中设置。例如：
$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>';
}
回答by Vinko Vrsalovic
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.
这真的是在庞大的数据集上执行的吗？如果不是，这应该以不同的方式解决。
回答by VolkerK
The parameter's effect is documented at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_join_size.
该参数的效果记录在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.
您应该更严格地过滤所涉及的记录（因此查询的每个部分涉及的记录较少）。如果可能的话，从表格开始，您可以在其中使用简单的 WHERE 子句过滤掉最多的记录。
回答by Sam
I've ran into the same problem. Its a drupal site so no surprise that it fell over.
我遇到了同样的问题。它是一个 Drupal 站点，因此它倒下也就不足为奇了。
It was an old style query, ie Select blah From table1, table2, table3 Where table1.id=table2.id And table2.some = 'thing'
这是一个旧式查询，即 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.
就像@VolkerK 所说，解决方案是将过滤 table2 结果的 where 子句移动到匹配 table1 到 table2 的那个之前（实际上是连接子句），从而减少需要在 table2 中匹配到 table1 的记录数量。
回答by Tum
For me the solution was to add an index key to all the columns the joins used to match.
对我来说，解决方案是为连接用来匹配的所有列添加一个索引键。
回答by Diamond Amie Ibifa
If you are using PDO driver, set the PDO::MYSQL_ATTR_INIT_COMMANDin your driver_optionsarray when constructing a new database handle
如果您使用的是 PDO 驱动程序，PDO::MYSQL_ATTR_INIT_COMMAND请在构造新的数据库句柄时在driver_options数组中设置
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'));
