Resolving MySQL Error 1104: Dealing with ‘ER_TOO_BIG_SELECT’

by

When working with MySQL, encountering Error 1104, also known as SQLSTATE: 42000 (ER_TOO_BIG_SELECT), can be a daunting experience. This error message indicates that a SELECT query is attempting to examine more rows than the MAX_JOIN_SIZE allows. Understanding and fixing this error involves a combination of optimizing your query and adjusting MySQL server settings.

Diagnosing Error 1104

The first step in diagnosing this problem is understanding the error message:

Error 1104: 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.

This message suggests that the complexity of the SELECT statement is too high, often due to a lack of proper indexing, an overly broad WHERE clause, or a join operation that is producing a Cartesian product.

Check Your Query

Before making any server configuration changes, review your query:

  • Ensure that your WHERE clause is as restrictive as possible to minimize the number of rows examined.
  • Verify that you have appropriate indexes on the columns used in the JOIN conditions and WHERE clauses.
  • Consider whether the join is necessary or if it can be rewritten in a more efficient manner.

Fixing Error 1104

If the query is optimized but still requires examining a large number of rows, you can adjust the server settings to allow the query to execute:

Using SET SQL_BIG_SELECTS

For a one-time operation where you know the query will need to process a large dataset, you can enable SQL_BIG_SELECTS:

SET SQL_BIG_SELECTS=1;

This command allows the current session to execute the SELECT without being restricted by MAX_JOIN_SIZE.

Adjusting SET SQL_MAX_JOIN_SIZE

Alternatively, if you need to set a specific limit for the maximum number of rows that can be examined, use:

SET SQL_MAX_JOIN_SIZE=#;

Replace # with the desired limit.

Permanent Configuration Changes

For permanent changes, you can set these variables in the MySQL configuration file (usually my.cnf or my.ini):

[mysqld]
sql_big_selects=1
max_join_size=#  # Replace # with the limit

Remember to restart the MySQL server after making changes to the configuration file.

Sample Code

Here’s an example of how to use these settings in a MySQL session:

-- Temporarily allow big selects for the current session
SET SQL_BIG_SELECTS=1;

-- Execute your complex SELECT query
SELECT * FROM large_table lt
JOIN another_large_table alt ON lt.id = alt.lt_id
WHERE lt.condition = 'value';

-- Alternatively, set a custom MAX_JOIN_SIZE for the current session
SET SQL_MAX_JOIN_SIZE=1000000;

-- Execute your SELECT query again
SELECT * FROM large_table lt
JOIN another_large_table alt ON lt.id = alt.lt_id
WHERE lt.condition = 'value';

Conclusion

By carefully examining your queries and considering the use of SQL_BIG_SELECTS or SQL_MAX_JOIN_SIZE, you can overcome the limitations imposed by MAX_JOIN_SIZE. Always ensure that any changes to server settings are made with a clear understanding of the implications for server performance and query execution.

Leave a Comment