What causes "ERROR 1104: SELECT would examine too many rows"?

Missing or ineffective indexes on JOIN fields - MySQL must examine all combinations of rows from both tables Queries without WHERE clauses or weak WHERE conditions that examine too much data Complex multi-table JOINs that produce massive intermediate result sets

Is the "ERROR 1104: SELECT would examine too many rows" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix ERROR 1104: SELECT would examine too many rows in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 162 views

MySQL Error 1104 occurs when a SELECT query would examine more rows than the max_join_size limit allows. This typically happens with unindexed joins on large tables. Fix it by optimizing your query with proper indexes, adding WHERE clauses, or temporarily enabling SQL_BIG_SELECTS.

Quick fix

Fixes ERROR 1104: SELECT would examine too many rows

EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

Run EXPLAIN to analyze the problematic query
Add indexes on JOIN fields
Add or improve WHERE clauses
Ensure JOIN columns have compatible data types

mysql — error

$EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';
ERROR 1104: SELECT would examine too many rows

What this error means

MySQL Error 1104 (ER_TOO_BIG_SELECT) is a safety limit that prevents queries from examining an excessive number of rows. When MySQL estimates that a SELECT statement would need to scan more than the configured max_join_size threshold (default: 4,294,967,295 rows), it blocks the query with this error. This error typically arises from complex JOIN operations on tables without proper indexes. MySQL must construct temporary result sets to perform joins, and without indexes on join fields, these temporary sets can become extremely large. The error is MySQL's way of warning you that the query may consume excessive CPU, memory, and I/O resources. In most cases, this error indicates a query optimization problem rather than a configuration issue. However, some legitimate queries may exceed the threshold if you're working with very large datasets.

Typical symptoms

Queries with multiple JOINs fail with ERROR 1104
SELECT statements run without WHERE clauses or with weak WHERE conditions fail
Queries that worked on smaller datasets fail after scaling data
Applications or frameworks (e.g., Joomla, Drupal) report SQL errors during database operations
JOIN operations between large unindexed columns fail

Common causes

Missing or ineffective indexes on JOIN fields - MySQL must examine all combinations of rows from both tables
Queries without WHERE clauses or weak WHERE conditions that examine too much data
Complex multi-table JOINs that produce massive intermediate result sets
Using JOIN fields that have different data types or collations, preventing index usage
Subqueries or derived tables that generate large intermediate datasets
Default max_join_size configuration on servers with very large tables

How to fix "ERROR 1104: SELECT would examine too many rows"

1Run EXPLAIN to analyze the problematic query

First, diagnose the query by running EXPLAIN to see how MySQL executes it:

sql

EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

Look for:
- type = ALL: Table scan, indicates no index is being used
- rows = large numbers: Shows estimated rows examined
- join type shown: Should be ref or eq_ref for good performance, not ALL

If you see type = ALL or very large row estimates, your query needs optimization.

2Add indexes on JOIN fields

The most common fix is to create indexes on the columns used in JOIN conditions:

sql

-- Index the foreign key column
CREATE INDEX idx_customer_id ON orders(customer_id);

-- If the referenced column isn't the primary key, index it too
CREATE INDEX idx_id ON customers(id);

-- For composite JOINs, create composite indexes
CREATE INDEX idx_order_lookup ON orders(customer_id, status);

After adding indexes, run EXPLAIN again to confirm MySQL now uses index lookups instead of table scans. The row count should drop dramatically.

3Add or improve WHERE clauses

Optimize your WHERE clause to examine fewer rows before the JOIN:

sql

-- BEFORE: No WHERE clause, examines all rows
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- AFTER: Restrictive WHERE clause reduces examined rows
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND o.status IN ('pending', 'processing');

The more rows you filter out before the JOIN, the fewer rows MySQL must examine.

4Ensure JOIN columns have compatible data types

JOIN performance degrades if the compared columns have different data types or collations:

sql

-- Check column definitions
DESCRIBE customers;  -- Does it show id as INT?
DESCRIBE orders;     -- Does it show customer_id as INT?

-- If types don't match, MySQL can't use indexes effectively
-- Convert or rebuild columns to match:
ALTER TABLE orders MODIFY customer_id INT;

-- Also verify collations match if using VARCHAR/TEXT
SHOW CREATE TABLE customers;
SHOW CREATE TABLE orders;

Mismatched types force implicit conversions, preventing index usage.

5Temporarily allow large selects if query is valid (use cautiously)

If you've optimized the query and confirmed it's correct but still hits the limit, temporarily enable large selects:

sql

SET SQL_BIG_SELECTS=1;

This disables the max_join_size check for the current session. Use this only as a temporary measure while investigating further optimizations.

WARNING: This should never be a permanent solution. Queries that examine billions of rows will cause performance degradation and resource exhaustion.

6Increase max_join_size if needed (permanent configuration)

If you have legitimate queries that need to examine more rows, increase the limit in your MySQL configuration file:

Edit my.cnf (Linux/macOS) or my.ini (Windows):

ini

[mysqld]
max_join_size=18446744073709551615
sql_big_selects=1

Then restart MySQL:

bash

# Linux
sudo systemctl restart mysql

# macOS with Homebrew
brew services restart mysql

Alternatively, set it dynamically (applies only to current session):

sql

SET max_join_size=18446744073709551615;
SET sql_big_selects=1;

NOTE: Only do this after optimizing your queries. Allowing unlimited large selects can crash your server.

7Refactor complex queries with subqueries or temporary tables

For very complex multi-table JOINs, break them into smaller steps:

sql

-- BEFORE: Single large JOIN that might hit the limit
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN payments p ON p.order_id = o.id
JOIN products prod ON prod.id = o.product_id
WHERE o.status = 'completed';

-- AFTER: Use subqueries and temporary tables
CREATE TEMPORARY TABLE recent_orders AS
SELECT id, customer_id, product_id
FROM orders
WHERE status = 'completed'
  AND created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY);

SELECT o.*, c.*, p.*, prod.*
FROM recent_orders o
JOIN customers c ON o.customer_id = c.id
JOIN payments p ON p.order_id = o.id
JOIN products prod ON prod.id = o.product_id;

Breaking the query into stages allows MySQL to optimize each step independently and reduces memory usage.

Advanced notes

Understanding max_join_size

The max_join_size variable limits the number of row combinations MySQL will examine during a JOIN. This is a safety feature: if a query would require MySQL to evaluate billions of row combinations, it likely indicates a logic error or missing index.

The default value (4,294,967,295 rows, or 2^32-1) is very high and exists mainly for safety. You rarely hit it unless you're working with extremely large datasets or have serious query/indexing problems.

SQL_BIG_SELECTS relationship

Setting SQL_BIG_SELECTS=0 enforces the max_join_size limit. Setting SQL_BIG_SELECTS=1 bypasses the check. Note that setting max_join_size to any value other than DEFAULT automatically sets SQL_BIG_SELECTS=0, re-enabling the limit.

Performance implications

If you frequently hit this error, it's a red flag: your queries are likely inefficient. Before increasing the limit, invest time in indexing and query optimization. A query that examines billions of rows will be slow regardless of whether MySQL allows it—you're just postponing the pain.

Application frameworks

CMS platforms like Joomla and Drupal sometimes generate inefficient queries during database checks or updates. If you encounter this error in a framework:
1. Update the framework to the latest version (often includes query optimizations)
2. Check if the framework has a configuration option to disable the problematic check
3. Only as a last resort, increase max_join_size server-wide

Frequently asked questions

What does "ERROR 1104: SELECT would examine too many rows" mean?: MySQL Error 1104 (ER_TOO_BIG_SELECT) is a safety limit that prevents queries from examining an excessive number of rows. When MySQL estimates that a SELECT statement would need to scan more than the configured max_join_size threshold (default: 4,294,967,295 rows), it blocks the query with this error. This error typically arises from complex JOIN operations on tables without proper indexes.
How do I fix "ERROR 1104: SELECT would examine too many rows"?: MySQL Error 1104 occurs when a SELECT query would examine more rows than the max_join_size limit allows. This typically happens with unindexed joins on large tables. Fix it by optimizing your query with proper indexes, adding WHERE clauses, or temporarily enabling SQL_BIG_SELECTS.
What causes "ERROR 1104: SELECT would examine too many rows"?: Missing or ineffective indexes on JOIN fields - MySQL must examine all combinations of rows from both tables Queries without WHERE clauses or weak WHERE conditions that examine too much data Complex multi-table JOINs that produce massive intermediate result sets
Is the "ERROR 1104: SELECT would examine too many rows" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.