Hey there! Great question—when dealing with million-row tables, even straightforward JOINs can start to drag if not optimized properly. Let’s walk through some better approaches and tweaks to your current double INNER JOIN setup:

1. Start with Index Tuning (Non-Negotiable!) #

Before trying any fancy query rewrites, make sure your database has the right indexes to avoid full-table scans. For your scenario:

• Table1: Create an index on field11 (the join key) that includes the field you need to fetch (field12). This avoids "bookmark lookups" back to the main table:

  CREATE INDEX idx_t1_f11_include_f12 ON Table1 (field11) INCLUDE (field12);
  

• Table2: Since pk2 is a primary key, it should already have an index—but add an index on field24 (the join key to Table3) that includes field23 and field24:

  CREATE INDEX idx_t2_f24_include_f23_f24 ON Table2 (field24) INCLUDE (field23, field24);
  -- Or, if your DB supports covering indexes for PK joins:
  CREATE INDEX idx_t2_pk2_include_f23_f24 ON Table2 (pk2) INCLUDE (field23, field24);
  

• Table3: Similar to Table1, create an index on pk3 (primary key, already indexed) that includes field31 and field34:

  CREATE INDEX idx_t3_pk3_include_f31_f34 ON Table3 (pk3) INCLUDE (field31, field34);
  

Covering indexes let the database pull all needed data directly from the index, skipping the main table entirely—this is a massive win for large datasets.

2. Rewrite Queries for Optimizer Efficiency #

Your double INNER JOIN is valid, but sometimes reordering joins or using CTEs/derived tables can help the optimizer choose a better execution plan. For example:

Option A: Pre-Join Smaller Tables First #

If Table2 or Table3 is smaller (or has more selective filters), join those first before joining to Table1:

SELECT 
  t1.field11, t1.field12,
  t2.field23, t2.field24,
  t3.field31, t3.field34
FROM Table2 t2
INNER JOIN Table3 t3 ON t2.field24 = t3.pk3
INNER JOIN Table1 t1 ON t1.field11 = t2.pk2;

Many optimizers will do this automatically, but explicitly writing it can help if your DB’s optimizer is being stubborn.

Option B: Use CTEs for Readability (and Potential Optimization) #

CTEs (Common Table Expressions) make queries easier to read, and some databases (like PostgreSQL) can optimize them as temporary in-memory tables:

WITH joined_t2_t3 AS (
  SELECT 
    t2.pk2, t2.field23, t2.field24,
    t3.field31, t3.field34
  FROM Table2 t2
  INNER JOIN Table3 t3 ON t2.field24 = t3.pk3
)
SELECT 
  t1.field11, t1.field12,
  jt2t3.field23, jt2t3.field24,
  jt2t3.field31, jt2t3.field34
FROM Table1 t1
INNER JOIN joined_t2_t3 jt2t3 ON t1.field11 = jt2t3.pk2;

Note: MySQL’s CTEs are treated as subqueries (not materialized), so performance here will match a standard JOIN—but readability is still a plus.

3. Batch Processing for Large Result Sets #

If you don’t need all million+ rows at once, split the query into smaller batches to reduce memory pressure and avoid locking up your database. Use a range on a primary key (avoid OFFSET, as it gets slow with large values):

-- First batch
SELECT 
  t1.field11, t1.field12, t2.field23, t2.field24, t3.field31, t3.field34
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.field11 = t2.pk2
INNER JOIN Table3 t3 ON t2.field24 = t3.pk3
WHERE t1.pk1 BETWEEN 1 AND 10000;

-- Next batch
SELECT ... 
WHERE t1.pk1 BETWEEN 10001 AND 20000;

This approach is especially useful for exporting data or processing results in chunks.

4. Materialized Views for Frequent Queries #

If this query runs often (and your data isn’t changing every second), a materialized view pre-computes and stores the join results. You can refresh it periodically (e.g., hourly, daily) to keep data up-to-date:

PostgreSQL Example: #

-- Create the materialized view
CREATE MATERIALIZED VIEW mv_query_results AS
SELECT 
  t1.field11, t1.field12, t2.field23, t2.field24, t3.field31, t3.field34
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.field11 = t2.pk2
INNER JOIN Table3 t3 ON t2.field24 = t3.pk3;

-- Refresh when needed
REFRESH MATERIALIZED VIEW mv_query_results;

-- Query the view like a table
SELECT * FROM mv_query_results;

MySQL Workaround (No Native Materialized Views): #

Create a regular table and use a cron job or event scheduler to refresh it periodically with the join query.

5. Check the Execution Plan #

No matter which approach you take, always run an execution plan to verify the database is using your indexes efficiently. Use:

• EXPLAIN for MySQL/PostgreSQL/SQL Server (shows the planned execution steps)
• EXPLAIN ANALYZE for PostgreSQL (runs the query and shows actual execution stats)
  Look for:
• Using index (means covering indexes are being used)
• No Using temporary or Using filesort (signs of inefficient sorting/aggregation)
• No Full Table Scan on large tables

Final Notes #

Your initial double INNER JOIN isn’t bad—it just needs tuning. Start with indexing, then test query rewrites or batch processing based on your use case. If the query is run frequently, materialized views are a game-changer.

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