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

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

-- 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);

-- 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');

-- 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;

SET SQL_BIG_SELECTS=1;

[mysqld]
max_join_size=18446744073709551615
sql_big_selects=1

# Linux
sudo systemctl restart mysql

# macOS with Homebrew
brew services restart mysql

SET max_join_size=18446744073709551615;
SET sql_big_selects=1;

-- 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;