As your backend scales, inefficient database queries will become the primary bottleneck slowing down your API. Learning how to analyze and optimize queries is what separates junior engineers from senior engineers.

Step 1 — The EXPLAIN Command

Never guess why a query is slow. Every major SQL and NoSQL database has an EXPLAIN command that reveals the 'Query Execution Plan' — telling you exactly how the database engine decided to fetch the data.

Explain Plansql
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 45;

-- Output will show if it did a "Seq Scan" (Sequential Scan - BAD! Scanned entire table)
-- Or if it did an "Index Scan" (Good! Used a B-Tree index to instantly find the row)

Step 2 — Strategic Indexing

Indexes act like the index at the back of a book, allowing the database to find data in O(log n) time instead of O(n) time.

Indexing Rules

  • Primary and Unique constraints automatically create indexes.
  • Add indexes to Foreign Keys (because you frequently JOIN on them).
  • Add indexes to columns used in WHERE, ORDER BY, and GROUP BY clauses.
  • Use Composite Indexes for queries that filter on multiple columns (e.g., WHERE status='active' AND created_at > '2023-01-01'). Order matters in composite indexes!

Step 3 — Defeating the N+1 Query Problem

This occurs when an ORM issues 1 query to fetch a list of N items, and then N additional queries to fetch a related entity for each item.

Fixing N+1 with Prisma ORMjavascript
// BAD: Causes N+1 (Prisma lazy fetching)
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: Eager Loading (1 Query using SQL JOIN)
const usersWithPosts = await prisma.user.findMany({
  include: { posts: true }
});

Step 4 — Pagination Strategies

OFFSET pagination gets slower the deeper you go because the database still has to scan and discard all the skipped rows.

Offset vs Cursorsql
-- BAD: Deep Offset (Has to scan 100,050 rows, discards 100,000, returns 50)
SELECT * FROM comments ORDER BY id DESC LIMIT 50 OFFSET 100000;

-- GOOD: Cursor Pagination (Uses index to jump directly to ID 95400, reads 50 rows)
SELECT * FROM comments WHERE id < 95400 ORDER BY id DESC LIMIT 50;

Step 5 — Select Only What You Need

Stop using SELECT *. Transferring unnecessary data wastes database RAM, CPU, network bandwidth, and application memory.

Projectionsql
-- BAD
SELECT * FROM users WHERE active = true;

-- GOOD (Projection)
SELECT id, name, email FROM users WHERE active = true;