SQL Performance and Anti Patterns Questions
Recognition and remediation of common SQL performance anti patterns and pitfalls, such as accidental cartesian joins, N plus one query patterns, inefficient correlated subqueries, using functions in WHERE clauses that prevent index use, SELECT star usage, lack of appropriate indexes, large unbounded sorts or aggregations, and poor join ordering. Covers methods to diagnose problems using execution plans, explain analyze, and rewriting queries for better performance and scalability.
HardTechnical
0 practiced
You must compute a distinct count of user_id across a massive dataset and current DISTINCT-based query is too slow. Propose optimizations including partition-wise aggregation, pre-aggregation, or approximate algorithms such as HyperLogLog. Explain accuracy trade-offs and how you'd validate and present approximation error to stakeholders.
EasyTechnical
0 practiced
Explain how missing indexes can cause slow queries. Given query SELECT SUM(total) FROM orders WHERE customer_id = 123 AND order_date >= '2024-01-01'; propose an index to improve performance and explain why column order matters for composite indexes. When might separate indexes be preferred?
MediumTechnical
0 practiced
You have this query on large tables: SELECT c.region, SUM(o.total) FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2024-01-01' GROUP BY c.region; Describe how to use EXPLAIN ANALYZE to find the slow part and give two concrete optimizations you would attempt (schema/index/query rewrite).
MediumTechnical
0 practiced
Explain why the optimizer's statistics (ANALYZE/collect stats) matter. Describe symptoms of stale or missing stats (bad plans, join order mistakes) and outline how you would automate or schedule statistics gathering after bulk loads or ETL. Include flags you might inspect to diagnose stats problems.
MediumTechnical
0 practiced
For a nightly sales dashboard, compare using materialized views versus precomputed aggregate tables refreshed nightly. Discuss freshness, concurrent refresh, storage cost, complexity of incremental refresh, and how each approach affects dashboard latency and maintenance.
Unlock Full Question Bank
Get access to hundreds of SQL Performance and Anti Patterns interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.