Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Query optimization: use indexes; avoid SELECT *; push filters early; use JOINs not subqueries; analyze execution plans; partition pruning; materialized views; statistics; limit result sets; avoid functions on indexed columns (e.g., WHERE YEAR(date)=2024 prevents index use—use...
Query optimization: use indexes; avoid SELECT *; push filters early; use JOINs not subqueries; analyze execution plans; partition pruning; materialized views; statistics; limit result sets; avoid functions on indexed columns (e.g., WHERE YEAR(date)=2024 prevents index use—use range instead). 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.
Red Flag: Optimizing without EXPLAIN or baseline. Pro-Move: 'EXPLAIN ANALYZE + composite index cut P99 80%; we measured write impact before rollout.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.