Performance & Optimization

• MySQL chooses the best join order using the optimizer.

• Adding STRAIGHT_JOIN forces MySQL to join tables in the written order.

• Useful for performance testing or when MySQL chooses a suboptimal path.

• Fetching unnecessary columns increases memory usage.

• Forces larger temporary tables, especially with GROUP BY or ORDER BY.

• Reduces query performance significantly.

• INNER JOIN is faster because MySQL can discard non-matching rows early.

• LEFT/RIGHT JOIN must preserve all rows from one table, reducing optimization opportunities.

• Expressions prevent MySQL from using indexes.

• Forces full table scans on both sides.

• A covering index contains all columns needed for the JOIN and SELECT.

• Allows MySQL to answer the query without touching the table.

• Boosts performance dramatically.

• Shows how MySQL executes the JOIN.

• Helps detect full table scans, bad indexes, temporary tables, file sorts, and join order issues.

• MySQL may create temporary in-memory or disk-based tables.

• Happens with ORDER BY, GROUP BY, DISTINCT, or joining unfiltered tables.

• Add appropriate WHERE filters early to reduce intermediate result sizes.

• Derived tables reduce overall scanned rows when used to pre-aggregate data.

• But avoid very large derived tables — they are materialized by MySQL.