Understanding EXPLAIN Plans

Index Types & Strategies

Join Optimization Strategies

Subquery Optimization

Table Partitioning Strategies

Query Rewriting Techniques

Statistics and Cardinality

Caching and Memory Management

Analyze a slow query using EXPLAIN ANALYZE and identify bottlenecks

Add appropriate indexes to optimize a multi-table JOIN query

Rewrite correlated subqueries as JOINs and measure improvement

Convert function-based WHERE clauses to index-friendly alternatives

Optimize a query with SELECT * by selecting only needed columns

Implement cursor-based pagination to replace OFFSET pagination

Create covering indexes to eliminate table lookups

Partition a large table and measure query performance improvement

Update database statistics and observe execution plan changes

Set up slow query logging and analyze the slowest queries

Walk through your process for optimizing a slow query from start to finish

Explain how database optimizers choose execution plans

What are the most common causes of slow queries and how do you fix them?

Why do functions on indexed columns prevent index usage?

When would you use a table scan instead of an index scan?

How do you decide which columns to index?

Explain the difference between covering indexes and regular indexes

What is the N+1 query problem and how do you solve it?

How do you optimize queries with multiple JOINs?

What tools and techniques do you use to monitor query performance in production?

Use The Index, Luke - Comprehensive indexing guide

PostgreSQL Performance Tuning Guide

MySQL High Performance by Baron Schwartz

SQL Server Execution Plans by Grant Fritchey

Database Internals by Alex Petrov

High Performance MySQL

PostgreSQL Query Optimization Documentation

Explain Plan Analysis Best Practices