How to Fix MySQL Error 1104: SELECT Would Examine Too Many Rows (max_join_size)

ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE clause and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay is raised when the MySQL query optimizer estimates that executing a query would require examining more rows than the current max_join_size session or global variable allows. The error symbol is ER_TOO_BIG_SELECT.

Impact

The query is rejected immediately — no rows are returned and no data is read from disk. This is a safety mechanism to prevent runaway queries from consuming excessive I/O or locking resources on shared or production servers.

Developers most commonly encounter this error when running ad-hoc queries against large tables without a WHERE clause, or after a schema change causes an existing index to stop being used. In application frameworks and ORMs (such as Laravel, Django ORM, or ActiveRecord), this surfaces as a database exception, typically wrapped in a generic query execution error, and can crash requests if not caught.

Common Causes

1. No WHERE clause on a large table. A bare SELECT * FROM large_table or SELECT COUNT(*) FROM large_table forces a full table scan. If the estimated row count exceeds max_join_size, the query is rejected.

2. Missing or unused index. The query has a WHERE clause, but no index covers the filtered column(s), so MySQL falls back to a full scan. After table growth, the optimizer may cross the max_join_size threshold even for queries that previously worked.

3. Cartesian join or under-constrained join. Joining two or more tables without a proper join condition (or with a condition that matches many rows) multiplies the estimated row count. For example, SELECT * FROM a, b without a WHERE clause produces a cross join whose estimated size is rows(a) * rows(b).

4. max_join_size set too low. A system administrator may have lowered max_join_size from its default (18446744073709551615, effectively unlimited) to throttle queries on a shared server. Queries that were fine before the configuration change now fail.

5. Outdated table statistics. If ANALYZE TABLE has not been run recently, the optimizer may overestimate the row count and incorrectly trigger the limit even for a well-indexed query.

Troubleshooting and Resolution Steps

1. Check the current max_join_size value and the query's estimated row count.

   SHOW SESSION VARIABLES LIKE 'max_join_size';
   SHOW GLOBAL  VARIABLES LIKE 'max_join_size';
   
   -- Estimate how many rows the query would examine
   EXPLAIN SELECT * FROM orders WHERE status = 'pending';
   

   Look at the rows column in the EXPLAIN output. If its product across all joined tables exceeds max_join_size, the query will be rejected.

2. Add an index on the filtered or joined column.

   -- Identify missing indexes
   EXPLAIN SELECT * FROM orders WHERE status = 'pending';
   
   -- Add a covering index
   ALTER TABLE orders ADD INDEX idx_status (status);
   
   -- Re-run EXPLAIN to confirm key usage
   EXPLAIN SELECT * FROM orders WHERE status = 'pending';
   

   After adding the index, the optimizer's row estimate should drop and the query should succeed.

3. Refresh table statistics if the index exists but is ignored.

   ANALYZE TABLE orders;
   

   Then re-run the query. Stale statistics can cause the optimizer to choose a full scan even when a suitable index is present.

4. Narrow the query with a WHERE clause or LIMIT.

   -- Instead of:
   SELECT * FROM events;
   
   -- Use date bounds or a LIMIT:
   SELECT * FROM events WHERE created_at >= '2024-01-01' LIMIT 1000;
   

5. Temporarily raise max_join_size for a specific session (use sparingly, and only when you are certain the query is safe):

   SET SESSION max_join_size = 1000000000;
   SELECT * FROM large_table WHERE category = 'archived';
   -- max_join_size resets automatically at session end
   

   To bypass the limit entirely for a session, use SQL_BIG_SELECTS:

   SET SESSION SQL_BIG_SELECTS = 1;
   SELECT * FROM large_table;
   

   Do not set SQL_BIG_SELECTS = 1 globally in a production environment; it defeats the purpose of the safety guard.

6. Permanently raise max_join_size globally (only for controlled environments):

   SET GLOBAL max_join_size = 10000000;
   

   Or add it to my.cnf / my.ini under [mysqld]:

   max_join_size = 10000000
   

Additional Information

• max_join_size is a session-level variable, so each connection can override it independently without affecting other sessions.
• The older alias sql_max_join_size is still accepted for backward compatibility but max_join_size is preferred.
• SQL_BIG_SELECTS is a session flag (not a size limit). Setting it to 1 disables the max_join_size check entirely for that session, regardless of how many rows the query would examine.
• The SQLSTATE code is 42000 (syntax error or access rule violation), the same class used for permission and syntax errors, so some client libraries may not distinguish it from other 42000 errors without inspecting the error number.
• In MySQL 8.0, EXPLAIN ANALYZE provides actual row counts after execution and can confirm whether statistics were the root cause.
• Cloud-managed MySQL offerings (Amazon RDS, Google Cloud SQL, PlanetScale) may set lower max_join_size defaults or restrict global variable changes, requiring index improvements rather than configuration overrides.

Frequently Asked Questions

Why did this error start appearing after a data import or after the table grew? As a table grows, the optimizer's row estimate for unindexed queries grows with it. A query that examined 900,000 rows before was fine under a max_join_size of 1,000,000; after the table doubled in size the estimate exceeds the threshold. The fix is almost always to add an appropriate index rather than raise the limit.

What is the difference between SQL_BIG_SELECTS and raising max_join_size? SQL_BIG_SELECTS = 1 disables the row-count check entirely for the current session. Raising max_join_size sets a new numeric ceiling. For one-off administrative queries, SQL_BIG_SELECTS is more convenient; for application queries, the correct fix is an index or query rewrite.

Does this error mean the query actually ran and scanned too many rows? No. MySQL rejects the query before execution based on the optimizer's estimated row count from table statistics. No rows are actually read. This is a pre-execution safety check, not a runtime limit.

Can an ORM silently swallow this error? Yes. Some ORMs catch database exceptions and return an empty result set or null instead of re-raising. If queries that used to return data suddenly return nothing, check application logs for suppressed exceptions with error code 1104.