SELECT查询超出MAX_JOIN_SIZE限制，多表连接问题求助

Hey there, let's work through this problem where your query's optimizer is misjudging row counts—leading to unexpected issues even though your final result is tiny. Here are actionable steps to resolve this:

1. Refresh Table Statistics First #

Outdated statistics are the #1 culprit for bad row estimations. Databases rely on these stats to guess how many rows will match your filters and joins, so make sure they're up-to-date:

• For MySQL/MariaDB: Run ANALYZE TABLE A, B, C;
• For PostgreSQL: Run ANALYZE A, B, C;
• For SQL Server: Run UPDATE STATISTICS A, B, C;

This gives the optimizer accurate data about how many rows match providerID = 200 in each table, which should help it choose a better join strategy.

2. Force a Better Join Order with Query Hints #

Since your filtered result sets are small (90, 60, 10 rows), we want the optimizer to start with the smallest set (C) and join outward, instead of potentially starting with a larger set that blows up intermediate results. Use a join hint to enforce this:

Example for MySQL/MariaDB (using STRAIGHT_JOIN): #

SELECT A.ID, B.ID, C.ID 
FROM C 
STRAIGHT_JOIN A ON C.aID = A.ID 
STRAIGHT_JOIN B ON A.ID = B.aID 
WHERE A.providerID = 200 
  AND B.providerID = 200 
  AND C.providerID = 200;

For PostgreSQL/SQL Server: #

Explicitly specify the join order in the FROM clause (most modern optimizers respect this when stats are accurate) or use vendor-specific hints like PostgreSQL's ORDERED to lock in the sequence.

3. Add Targeted Composite Indexes #

Missing or inefficient indexes can both slow down your query and throw off the optimizer's row estimates. Create composite indexes that cover your filter and join columns:

• For table A: CREATE INDEX idx_A_providerID_ID ON A (providerID, ID);
  This lets the database quickly fetch all IDs where providerID = 200 without scanning the whole table.
• For table B: CREATE INDEX idx_B_providerID_aID_ID ON B (providerID, aID, ID);
  Covers the filter on providerID, the join on aID, and includes ID so the database doesn't need to look up the row again.
• For table C: CREATE INDEX idx_C_providerID_aID_ID ON C (providerID, aID, ID);
  Same logic as table B—covers all needed columns in one index.

These indexes will speed up filter and join operations, and give the optimizer precise counts of matching rows.

4. Split the Query to Reduce Intermediate Results #

If the optimizer still isn't behaving, split the query into smaller parts to force it to handle filtered sets first. Use a CTE or subquery to isolate the filtered rows from A, then join to B and C:

WITH filtered_A AS (
  SELECT ID FROM A WHERE providerID = 200
)
SELECT fa.ID, B.ID, C.ID
FROM filtered_A fa
INNER JOIN B ON fa.ID = B.aID AND B.providerID = 200
INNER JOIN C ON fa.ID = C.aID AND C.providerID = 200;

This guarantees that we only work with the 90 rows from A first, so the intermediate joins (90 × 60, then ×10) are way below your MAX_JOIN_SIZE limit.

5. Tweak MAX_JOIN_SIZE (Last Resort) #

You mentioned BIG_SQL_QUERY didn't help, but if you've fixed stats and indexes and still hit limits, you can temporarily bump MAX_JOIN_SIZE for your session:

• For MySQL/MariaDB: SET SESSION MAX_JOIN_SIZE = 10000000; (set to a value just above your expected intermediate row count)

But remember: This is a band-aid. The real fix is getting the optimizer to choose the right plan using the steps above.

内容的提问来源于stack exchange，提问作者dark_982