How would you optimize a slow-running SQL query?

To optimize a slow SQL query, I'd systematically analyze its execution plan to identify bottlenecks, then apply targeted improvements like indexing, query refactoring, and leveraging database features such as partitioning, always measuring performance before and after changes.

Optimization Steps

Analyze Execution Plan (EXPLAIN / EXPLAIN ANALYZE): This is the crucial first step. It reveals how the database executes the query, highlighting expensive operations like full table scans, large sorts, and inefficient joins.

    EXPLAIN ANALYZE SELECT user_name, order_total FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.registration_date > '2023-01-01';
    

Indexing: Create indexes on columns frequently used in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY clauses. Indexes allow the database to quickly locate relevant rows without scanning the entire table. Consider composite indexes for multi-column filters.

Statistics: Ensure database statistics are up-to-date (UPDATE STATISTICS). The query optimizer relies on these to estimate data distribution and cardinality, making informed decisions about the most efficient execution plan.

Query Refactoring:

* EXISTS vs IN: For subqueries, EXISTS can often be more performant than IN because EXISTS stops processing upon finding a match. * CTEs vs Subqueries: While CTEs improve readability, their performance relative to subqueries can vary by database optimizer. Avoid SELECT : Explicitly select only necessary columns to reduce data transfer and processing.

Partitioning and Clustering: For large tables, partition data based on frequently filtered columns (e.g., date). This enables "partition pruning," where the database only scans relevant partitions. Systems like Snowflake use micro-partitions and clustering keys for similar benefits.

Join Order and Types: The optimizer usually determines the best join order. However, sometimes rewriting queries to join smaller, filtered datasets first can improve performance.

Concrete Example & Trade-offs

If a query joining users and orders tables on user_id is slow, an EXPLAIN plan might show a full table scan on orders. Creating an index on orders.user_id would be a primary optimization.

It's crucial to measure performance before and after each change and focus on the biggest bottleneck first. While indexing is powerful, avoid over-indexing as it increases write operation costs (inserts, updates, deletes) and storage overhead, and can sometimes confuse the optimizer.

In the interview, also mention: The iterative nature of optimization, emphasizing that it's a process of identifying, implementing, and validating changes.