How would you optimize a SQL query for better performance when working with large datasets?

SQL query optimization for large datasets: (1) Indexing—create indexes on filter and join columns; avoid over-indexing on write-heavy tables. (2) Partitioning—partition by date, region, or key columns to enable partition pruning. (3) Avoid SELECT —select only needed columns. (4) Push filters early—apply WHERE before JOINs. (5) Replace subqueries with JOINs or CTEs. (6) Use EXPLAIN to analyze execution plans. (7) Denormalize where read performance outweighs storage. (8) Consider materialized views for repeated aggregations. (9) Tune parallelism and resource allocation. Example: Instead of SELECT FROM huge_table, use SELECT col1, col2 FROM huge_table WHERE partition_date >= '2024-01-01' AND region = 'US'; Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.