MySQL - SQL_BIG_SELECTS

MySQL - SQL_BIG_SELECTS

提问by Matt

Hey, I've been investigating SQL_BIG_SELECTS, but the MySQL documentation so far has been pretty unhelpful. I'm looking for some insight as to preventing errors like the one below from appearing.

嘿，我一直在研究 SQL_BIG_SELECTS，但到目前为止 MySQL 文档一直没有帮助。我正在寻找一些有关防止出现以下错误的见解。

ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

错误 1104：SELECT 会检查太多记录并且可能需要很长时间。检查您的 WHERE 并使用 SET OPTION SQL_BIG_SELECTS=1 如果 SELECT 没问题

1. At how many rows does MySQL decide that a query is a "BIG SELECT"?
2. Will proper indexing usually solve this issue?
3. Is SQL_BIG_SELECTS considered a "last resort", or is it good practice?
4. How would someone set "SQL_BIG_SELECTS=1" in configuration (without having to execute the query)?
5. Are there any other alternatives worth knowing?

1. MySQL 在多少行决定查询是“BIG SELECT”？
2. 正确的索引通常会解决这个问题吗？
3. SQL_BIG_SELECTS 被认为是“最后的手段”，还是好的做法？
4. 有人将如何在配置中设置“SQL_BIG_SELECTS=1”（无需执行查询）？
5. 还有其他值得了解的替代方案吗？

Thanks in advance!

提前致谢！

回答by jonstjohn

1. MySQL determines whether or not a query is a 'big select' based on the value of 'max_join_size'. If the query is likely to have to examine more than this number of rows, it will consider it a 'big select'. Use 'show variables' to view the value of the max join size.

2. I believe that indexing and particular a good where clause will prevent this problem from occuring.

3. SQL_BIG_SELECTS is used to prevent users from accidentally executing excessively large queries. It is okay to set it to ON in mysql.cnf or using the command-line option at startup.

4. You can set SQL_BIG_SELECTS in my.cnf or at server startup. It can also be set on a session basis with SET SESSION SQL_BIG_SELECTS=1.

5. Not that I can think of. I would just check your query to make sure that you really need to use it. Our servers have it turned on by default, and max_join_size is very large.

1. MySQL 根据“max_join_size”的值确定查询是否为“大选择”。如果查询可能需要检查的行数超过此数量，则它会将其视为“大选择”。使用“show variables”查看最大连接大小的值。

2. 我相信索引和特别好的 where 子句将防止这个问题的发生。

3. SQL_BIG_SELECTS 用于防止用户意外执行过大的查询。可以在 mysql.cnf 中将其设置为 ON 或在启动时使用命令行选项。

4. 您可以在 my.cnf 中或在服务器启动时设置 SQL_BIG_SELECTS。它也可以基于会话进行设置SET SESSION SQL_BIG_SELECTS=1。

5. 不是我能想到的。我只会检查您的查询以确保您确实需要使用它。我们的服务器默认开启它，并且 max_join_size 非常大。

回答by Bing Liu

You cannotset SQL_BIG_SELECTSin my.cnf or at server startup as it is a session only parameter. I am using MySQL 5.0.60.

您不能SQL_BIG_SELECTS在 my.cnf 或服务器启动时设置，因为它是一个仅限会话的参数。我正在使用 MySQL 5.0.60。

回答by Rubendob

As someone has post before, you can not set SQL_BIG_SELECTS on my.cnf at server startup. This kind of variable does not support that.

由于之前有人发过帖子，您不能在服务器启动时在 my.cnf 上设置 SQL_BIG_SELECTS。这种变量不支持。

I had a same problem with a Symfony application showing this annoying error:

我在 Symfony 应用程序中遇到了同样的问题，显示了这个烦人的错误：

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

But you can increase the number of varialbe

但是你可以增加变量的数量

max_join_size which is related to sql_big_selects

与 sql_big_selects 相关的 max_join_size

I was able to fix it executing a command as privileged mysql user:

我能够修复它以特权 mysql 用户身份执行命令：

# SET GLOBAL max_join_size=18446744073709551615;

Or you can include it in my.cnf because max_join_size is allowed to set up in configuration file

或者你可以将它包含在 my.cnf 中，因为允许在配置文件中设置 max_join_size

Well, I hope this can help someone else.

好吧，我希望这可以帮助别人。

回答by Zaffar Saffee

I had more than 2000k records in db, and my query was big-select with exhaustive comparison for duplication removal and updation of certain field...I was told the same by mysql(current version on answer date), and I ended up using indexon 2 fields involved in whereclause...this should help others too...steps which worked for me were:

我有超过2000K记录数据库，和我的查询与重复排除和某些领域的更新用详尽的比较是大选择......我被告知由同一mysql（当前版本的答案为准），我结束了使用index上where条款中涉及的 2 个字段......这也应该对其他人有所帮助......对我有用的步骤是：

1. Set Index on field
2. ANALYZE TABLE
3. run the query

1. 在字段上设置索引
2. 分析表
3. 运行查询

HTH

HTH

回答by Musa

Following command works for me

以下命令对我有用

SET GLOBAL max_join_size=18446744073709551615;

But what do i need to put in my.cnf file instead of the command? Btw, i'm using "5.6.12 MySQL Community Server"

但是我需要在 my.cnf 文件中而不是命令中放入什么？顺便说一句，我正在使用“5.6.12 MySQL 社区服务器”

回答by Juan Manuel Alcorta Villarreal

SET SESSION SQL_BIG_SELECTS =1;# MySQL returned an empty result set (i.e. zero rows).
SELECT a.`HACtrl` , b.`HBCtrl` , c.`HDCtrl`
FROM `HO110BLote` AS a
LEFT JOIN `HO113BPago` AS b ON ( b.HBHACtrl = a.HACtrl )
LEFT JOIN `HO113BRecibos` AS c ON ( c.HDHBCtrl = b.HBCtrl )
WHERE HAManzana = '03'
AND HALote = '09'
LIMIT 0 , 100