Optimization and Performance

❌ N+1 — one query per user

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute("SELECT id, name FROM users")
users = cursor.fetchall()

for user_id, name in users:
    cursor.execute(
        "SELECT title FROM articles WHERE user_id = ?",
        (user_id,),
    )
    articles = cursor.fetchall()
    # 1 + N queries...

✅ A single query with a JOIN

cursor.execute("""
    SELECT u.id, u.name, a.title
    FROM users u
    LEFT JOIN articles a ON a.user_id = u.id
""")
rows = cursor.fetchall()

# SQL run by the N+1 code:
# SELECT id, name, email FROM users;
# SELECT title FROM articles WHERE user_id = 1;
# SELECT title FROM articles WHERE user_id = 2;
# ...
# SELECT title FROM articles WHERE user_id = 500;