**Performance Benefits:**

1. Early Filtering:

- Filter in derived table before expensive operations

- Reduces data volume for outer query

- Can use indexes more effectively

Example: Filter 1M rows to 10K in derived table

Then join 10K rows (fast) vs joining 1M rows (slow)

2. Pre-Aggregation:

- Aggregate before joining

- Reduces join size dramatically

- Much faster than aggregating after join

Example: Aggregate 1M orders to 10K customers

Then join 10K rows vs 1M rows

3. Breaking Complex Logic:

- Optimizer can better optimize simple steps

- Each step can use appropriate indexes

- Easier to identify bottlenecks

**Performance Costs:**

1. Materialization:

- Some databases create temp table for derived table

- Costs time and memory

- Check execution plan

2. No Indexes:

- Derived tables can't have indexes

- If outer query filters derived table, can be slow

- Solution: Filter in derived table, not outer query

3. Repeated Execution:

- If derived table referenced multiple times, may execute multiple times

- Solution: Use CTE (can be optimized better)

**Optimization Strategies:**

1. Filter Early:

Put WHERE clauses in derived table, not outer query

2. Aggregate Early:

GROUP BY in derived table before joining

3. Select Only Needed Columns:

Don't use SELECT * if you only need few columns

4. Use Indexes on Base Tables:

Derived table can't have indexes, but base tables can

5. Test with EXPLAIN:

Verify derived table is optimized as expected