SQL Optimization and Performance Interview: Advanced Guide

In the world of data-driven applications, SQL performance can make or break your system. A poorly written query can bring a production database to its knees, causing timeouts and frustrated users. That's why SQL optimization is a favorite topic in technical interviews, especially for backend and data engineering roles. Interviewers want to see not just that you can write SQL, but that you understand how databases execute queries and how to make them fast.

This guide covers the most common SQL optimization interview questions, from indexing strategies to query rewriting techniques. You'll learn how to read execution plans, avoid common pitfalls like N+1 queries and unnecessary full table scans, and apply best practices like covering indexes and keyset pagination. Each section includes a real interview question with a step-by-step solution, complexity analysis, and tips for explaining your reasoning during an interview.

Whether you're preparing for a FAANG interview or a senior developer role, mastering these concepts will set you apart. Let's dive into the advanced world of SQL performance.

Understanding Execution Plans

An execution plan shows how the database engine will execute your query. It's the first thing you should check when optimizing. Use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see the plan. Look for sequential scans (Seq Scan) which indicate full table scans, and note the estimated rows vs actual rows. A large discrepancy suggests outdated statistics.

Example: For a query filtering on a non-indexed column, you'll see a Seq Scan. Adding an index changes it to an Index Scan. In an interview, explain that you always start with EXPLAIN to identify bottlenecks.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

Indexing Strategies

Indexes are the most powerful tool for SQL performance. Understand different types: B-tree (default), Hash, GiST, GIN, and BRIN. For typical equality and range queries, B-tree is best. Composite indexes (multiple columns) are useful for queries filtering on multiple columns. The order of columns matters: put the most selective column first.

Covering indexes include all columns needed by a query, allowing index-only scans. Partial indexes (WHERE clause) are great for frequently filtered subsets. In an interview, be ready to design an index for a given query pattern.

Example: For a query like SELECT name, email FROM users WHERE status = 'active' ORDER BY created_at DESC, a composite index on (status, created_at) INCLUDING (name, email) would be ideal.

CREATE INDEX idx_users_status_created ON users (status, created_at DESC) INCLUDE (name, email);

Query Rewriting Techniques

Sometimes rewriting a query yields dramatic improvements. Common techniques: - Replace subqueries with JOINs when possible (but not always). - Use EXISTS instead of IN for large subquery results. - Avoid SELECT *; specify only needed columns. - Use UNION ALL instead of UNION if duplicates are acceptable. - Use aggregate functions with GROUP BY instead of multiple subqueries.

Example: Instead of SELECT FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'vip'), use a JOIN: SELECT o. FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'vip'. This allows the optimizer to use indexes more effectively.

-- Slow subquery
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'vip');

-- Faster JOIN
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'vip';

Pagination Optimization

Traditional pagination using OFFSET and LIMIT becomes slow for large offsets because the database still scans all rows up to the offset. Keyset pagination (also called seek method) uses a WHERE clause on a unique column to skip rows.

Example: Instead of SELECT FROM orders ORDER BY id LIMIT 10 OFFSET 100000, use SELECT FROM orders WHERE id > 100000 ORDER BY id LIMIT 10. This uses an index on id and is constant time regardless of page number.

In an interview, explain that keyset pagination is preferred for large datasets, but requires a unique sort column.

-- Slow: OFFSET pagination
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;

-- Fast: Keyset pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

Handling N+1 Queries

The N+1 query problem occurs when an application executes one query to fetch a list of items, then for each item executes another query. This is common in ORMs like Hibernate or Django ORM. The solution is to use eager loading (JOINs) or batch queries.

Example: Fetching all customers and their orders. Instead of looping over customers and querying orders for each, use a single JOIN: SELECT c., o. FROM customers c LEFT JOIN orders o ON c.id = o.customer_id. Or use two queries: one for customers, another for orders with WHERE customer_id IN (...).

In an interview, recognize N+1 as a performance anti-pattern and propose solutions.

-- N+1: One query for customers, then N queries for orders
SELECT * FROM customers; -- 1 query
-- For each customer: SELECT * FROM orders WHERE customer_id = ?; -- N queries

-- Solution: JOIN
SELECT c.*, o.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

Common Pitfalls and Anti-Patterns

In an interview, demonstrate awareness of these pitfalls and how to avoid them.

-- Bad: function on column prevents index
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

-- Good: range query uses index
SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';