Database Query Optimization Intuition

Format: Understand the N+1 problem and the concept of indexes.

N+1 Problem:

Scenario: Display 10 articles, each showing the author's name.

Bad approach (11 queries):
  Query 1: Fetch 10 articles
  Queries 2-11: Fetch the author for each article separately

Good approach (2 queries):
  Query 1: Fetch 10 articles
  Query 2: Fetch all authors at once

Index analogy: A database index = a book's table of contents. Without a table of contents, finding a word means flipping through the entire book. With a table of contents, you go directly to the right page.

Exercise: Do the following queries need an index?

1. Find a user by user ID -> ?
2. Sort articles by creation time -> ?
3. Search for a user by email -> ?
4. Full-text search of article content -> ?