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. <blockquote> 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 </blockquote> <ol> <li>At how many rows does MySQL decide that a query is a "BIG SELECT"?</li> <li>Will proper indexing usually solve this issue?</li> <li>Is SQL_BIG_SELECTS considered a "last resort", or is it good practice?</li> <li>How would someone set "SQL_BIG_SELECTS=1" in configuration (without having to execute the query)?</li> <li>Are there any other alternatives worth knowing?</li> </ol> Thanks in advance!

<ol> <li>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.</li> <li>I believe that indexing and particular a good where clause will prevent this problem from occuring.</li> <li>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.</li> <li>You can set SQL_BIG_SELECTS in my.cnf or at server startup. It can also be set on a session basis with <code>SET SESSION SQL_BIG_SELECTS=1</code>.</li> <li>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.</li> </ol>

You cannot set <code>SQL_BIG_SELECTS</code> in my.cnf or at server startup as it is a session only parameter. I am using MySQL 5.0.60.

MySQL - SQL_BIG

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.

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

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

Thanks in advance!

How do I set system variables in MySQL?

System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it.

What is Net_read_timeout in MySQL?

On MySQL website net_read_timeout is describe as "The number of seconds to wait for more data from a connection before aborting the read" .

What is MySQL Thread_stack?

The thread_stack is the stack size for each thread. If the stack size is too small, it limits the complexity of SQL statements, the recursion depth of stored procedures and other memory-consuming actions. MySQL 5.7 defaults the stack size to 192KB on 32-bit platforms and 256KB on 64-bit systems.

How do I change global variables in MySQL?

To assign a value to a global system variable, precede the variable name by the GLOBAL keyword or the @@GLOBAL. qualifier: SET GLOBAL max_connections = 1000; SET @@GLOBAL.

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.
I believe that indexing and particular a good where clause will prevent this problem from occuring.
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.
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.
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.

You cannot set SQL_BIG_SELECTS in my.cnf or at server startup as it is a session only parameter. I am using MySQL 5.0.60.

MySQL - SQL_BIG_SELECTS

Tags:

select

mysql

Matt

People also ask

2 Answers

jonstjohn

Bing Liu

Recent Activity

Donate For Us