5 SQL Query Optimization Questions & Techniques for Technical Interview

Writing working queries is only the first step. In high-traffic systems, database index performance dictates system latency. Practice hands-on queries in our SQL Playground at /sql/ or test your knowledge with SQL practice tracks at /sql/sql-design-admin/.

1. Avoid SELECT * in production

Wildcard reads force disk operations, bypassing coverage indexes and loading unused text data. Specify columns directly to reduce memory and execution bounds.

2. Understand Index Coverage

An index creates a balanced-tree structure on specified columns. Lookups using indexed columns in WHERE filters take O(log N) time, whereas non-indexed comparisons trigger full table scans running in O(N) time.

3. Use EXPLAIN to Read Execution Plans

Always inspect execution plans with EXPLAIN ANALYZE. Look for sequential table scans on large data tables and add composite indexes to eliminate expensive sorting steps.

EXPLAIN ANALYZE
SELECT * FROM Orders
WHERE CustomerID = 42
  AND OrderDate >= '2026-01-01';

EXPLAIN ANALYZE runs the query and shows you the actual execution plan with row counts and timing. Look for: (1) 'Seq Scan' on large tables - this means no index is being used; (2) high 'cost' estimates on inner loops; (3) large discrepancies between 'rows estimated' and 'rows actual' - these indicate stale statistics that may cause the optimizer to choose a bad plan. Learning to read EXPLAIN output is a mandatory skill for senior data engineering roles.

4. Index Design Principles

A composite index on (CustomerID, OrderDate) can satisfy queries that filter on CustomerID alone, on both CustomerID and OrderDate, but NOT on OrderDate alone. This is the 'left-prefix rule.' When designing indexes, order the columns from most selective (highest cardinality) to least selective, unless your queries require a specific order for range scans.

-- Create a composite index for this common query pattern
CREATE INDEX idx_orders_customer_date
ON Orders (CustomerID, OrderDate DESC);

-- This query can use the index efficiently:
SELECT * FROM Orders
WHERE CustomerID = 42
ORDER BY OrderDate DESC
LIMIT 10;

5. Avoid Functions on Indexed Columns in WHERE

-- BAD: function call prevents index use
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- GOOD: range condition can use index
SELECT * FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

Wrapping an indexed column in a function call disables the index because the database must compute the function for every row before it can evaluate the condition. Rewrite such conditions as range comparisons when possible. This is one of the most impactful single optimizations you can apply to a slow query in production.

Interview Strategy: Talking About Performance

In data engineering interviews, performance questions often start with 'You have a query that takes 30 seconds on a 50 million row table. How would you approach optimizing it?' Structure your answer: (1) get the execution plan with EXPLAIN, (2) identify table scans on large tables, (3) check for missing indexes on WHERE/JOIN/ORDER BY columns, (4) consider query rewriting (eliminate functions on indexed columns, push filters before aggregations), (5) consider schema changes (partitioning, materialized views). Demonstrating this systematic approach is more valuable than knowing any single optimization trick.