⚡ Advanced SQL Fundamentals — Optimization & Performance

Master the advanced SQL techniques that separate good queries from great ones.

🎯 Query Execution Plans & EXPLAIN

Understanding how SQLite executes queries is key to optimization.

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

# See the execution plan
cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE age > 25")
plan = cursor.fetchall()
print(plan)
# Output shows: Full scan vs index lookup

# EXPLAIN shows:
# (0, 0, 0, 'SCAN users') = Full table scan (slow)
# (0, 0, 0, 'SEARCH users USING INDEX idx_age') = Index lookup (fast)

🔍 Indexing Strategy

Without indexes, SQLite scans every row. With indexes, it jumps directly to matching rows.

# Create an index on age column
cursor.execute("CREATE INDEX idx_age ON users(age)")

# Composite index for multiple columns
cursor.execute("CREATE INDEX idx_user_age_city ON users(age, city)")

# Check what indexes exist
cursor.execute("PRAGMA index_info(idx_age)")

# Efficient query (uses index)
cursor.execute("SELECT * FROM users WHERE age > 25")

# Remove unused index
cursor.execute("DROP INDEX idx_age")

When to index:

• ✅ Columns frequently used in WHERE clauses

• ✅ Columns used in JOIN conditions

• ✅ Columns used in ORDER BY

• ❌ Columns rarely searched on (wastes disk space)

• ❌ Small tables (full scans are fast enough)

💡 Complex Query Patterns

Compound Conditions

# OR conditions (less efficient - may not use index)
cursor.execute("""
    SELECT * FROM users
    WHERE age > 25 OR city = 'New York'
    -- SQLite may do full scan here
""")

# Better: Use UNION for OR queries with indexes
cursor.execute("""
    SELECT * FROM users WHERE age > 25
    UNION
    SELECT * FROM users WHERE city = 'New York'
    -- Can use two separate indexes
""")

# AND conditions (more index-friendly)
cursor.execute("""
    SELECT * FROM users
    WHERE age > 25 AND city = 'New York'
    -- Can use composite index
""")

CASE Statements

cursor.execute("""
    SELECT
        name,
        CASE
            WHEN age < 18 THEN 'Minor'
            WHEN age < 65 THEN 'Adult'
            ELSE 'Senior'
        END as age_group
    FROM users
""")

String Pattern Matching

# LIKE queries (uses GLOB or LIKE)
cursor.execute("SELECT * FROM users WHERE name LIKE 'A%'")  # Starts with A
cursor.execute("SELECT * FROM users WHERE email LIKE '%@gmail.com'")  # Ends with

# GLOB (case-sensitive, more pattern options)
cursor.execute("SELECT * FROM users WHERE name GLOB '[A-M]*'")

⚙️ Query Performance Techniques

1. ANALYZE & Statistics

# Gather statistics for query planner
cursor.execute("ANALYZE")

# This helps SQLite choose better query plans by understanding:
# - How many distinct values in each column
# - Data distribution
# - Table sizes

2. Batch Operations (Much Faster)

# Slow: Individual inserts
for user in users:
    cursor.execute("INSERT INTO users VALUES (?, ?, ?)", user)
conn.commit()

# Fast: Batch insert with executemany
cursor.executemany("INSERT INTO users VALUES (?, ?, ?)", users)
conn.commit()

# 100x faster for large datasets!

3. Temporary Indexes

# Create index just for this session (removed on close)
cursor.execute("CREATE TEMP INDEX temp_idx ON users(email)")

# Do fast lookups
cursor.execute("SELECT * FROM users WHERE email = ?", (email,))

# Index automatically deleted when connection closes

🚀 PRAGMA Optimizations

# Enable query cache
cursor.execute("PRAGMA query_only = OFF")

# Synchronous = OFF speeds up writes (trade: less crash-safe)
cursor.execute("PRAGMA synchronous = OFF")  # Fast but risky
cursor.execute("PRAGMA synchronous = NORMAL")  # Good balance
cursor.execute("PRAGMA synchronous = FULL")  # Safest but slow

# Journal mode affects performance
cursor.execute("PRAGMA journal_mode = WAL")  # Better for concurrent access
cursor.execute("PRAGMA journal_mode = DELETE")  # Default, slower

# Memory cache size (in pages)
cursor.execute("PRAGMA cache_size = 10000")  # More memory = faster

# Foreign key enforcement
cursor.execute("PRAGMA foreign_keys = ON")  # Enable relational integrity

📊 EXPLAIN QUERY PLAN Examples

# Fast: Uses index
cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 5")
# Result: SEARCH users USING INDEX sqlite_autoindex_users_1

# Slow: Full table scan
cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice'")
# Result: SCAN users (no index on name)

# Add index to fix it
cursor.execute("CREATE INDEX idx_name ON users(name)")
cursor.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice'")
# Result: SEARCH users USING INDEX idx_name (much faster!)

🔑 Key Takeaways

• ✅ Use EXPLAIN QUERY PLAN to diagnose slow queries

• ✅ Index frequently searched columns

• ✅ Use composite indexes for multiple-column WHERE clauses

• ✅ Batch operations with executemany() are 100x faster

• ✅ PRAGMA settings significantly impact performance

• ✅ ANALYZE helps query planner optimize

• ✅ Monitor execution plans as data grows

Ready to practice? Challenges | Quiz