MySQL - SQL_BIG_SELECTS

Question

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

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?

Thanks in advance!

Answer 1

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.