SQL Optimization — High-Impact Summary

---

Where SQL Becomes Slow (Root Causes)

CPU, Memory, Disk, Network

Problem	Example
Full table scan	Missing index
Too much data	SELECT *
Bad joins	Wrong join order
Blocking	Locks
Poor schema	Wrong data types

---

Indexes (MOST IMPORTANT)

“Indexes trade space for speed.”

What Indexes Do

• Speed up WHERE / JOIN / ORDER BY
• Avoid full table scans

When to Use Index

✅ Columns used in:

• WHERE
• JOIN
• ORDER BY
• GROUP BY

When NOT to Use

❌ Columns:

• Updated very frequently
• Low selectivity (e.g. IsActive)

“Indexes improve reads but slow down writes.”

---

Common Index Types

Index	Use Case
Clustered	Physical row order (PK)
Non-clustered	Lookup
Composite	Multiple columns
Covering	Query fully satisfied by index

🧠 Composite Index Rule

Order matters: (UserId, CreatedAt) ≠ (CreatedAt, UserId)

---

Query Writing Best Practices

❌ Bad

SELECT *
FROM Orders
WHERE YEAR(CreatedAt) = 2024

✅ Good

SELECT Id, Total
FROM Orders
WHERE CreatedAt >= '2024-01-01'
  AND CreatedAt < '2025-01-01'

🧠 Rules to Remember

• Avoid SELECT *
• Avoid functions on indexed columns
• Filter early
• Return only what you need

---

JOIN Optimization

“Join less, join smart.”

Tips

• Use INNER JOIN when possible
• Join on indexed columns
• Avoid unnecessary joins
• Ensure join columns have same data type

“Most slow queries are caused by bad joins.”

---

WHERE Clause Optimization

“Filter early, filter precisely.”

Best Practices

• Use indexed columns
• Avoid LIKE '%abc'
• Avoid OR when possible
• Prefer EXISTS over IN for large datasets

---

GROUP BY & Aggregation

“Aggregate late, not early.”

Tips

• Filter before grouping
• Index group columns
• Avoid grouping large result sets

---

Pagination Optimization (Very Common)

❌ OFFSET (slow on large tables)

OFFSET 100000 ROWS

✅ Keyset Pagination (fast)

WHERE Id > @LastId
ORDER BY Id
LIMIT 20

“Keyset pagination scales better than OFFSET.”

---

Execution Plan (Interview Favorite)

“Database tells you the truth.”

• Use EXPLAIN
• Look for:
  • Table scan ❌
  • Index seek ✅
  • High cost operators

“Execution plans show how the database executes the query.”

---

Locking & Concurrency

“Reads block writes, writes block reads.”

Problems

• Long transactions
• Table locks
• Deadlocks

Fixes

• Keep transactions short
• Use proper isolation levels
• Index foreign keys

---

Schema Design Matters

“Bad schema = slow queries forever.”

Best Practices

• Correct data types
• Normalize for writes
• Denormalize for heavy reads (carefully)
• Add FK indexes

---

Caching (Huge Performance Gain)

“Best query is no query.”

• Application cache
• Redis
• Query result cache

“Caching reduces database load more than any query tweak.”

---

EF Core / ORM Optimization (Since You Use .NET)

“Control what EF generates.”

Tips

.AsNoTracking()
.IgnoreAutoIncludes()
.Select(...)

• Avoid N+1 queries
• Use projections
• Log generated SQL

---

30-Second Interview Answer (Gold)

“SQL optimization focuses on reducing query cost by using proper indexes, writing efficient queries, minimizing data retrieval, and understanding execution plans. Most performance issues come from missing indexes, poor joins, and returning too much data. Tools like execution plans and proper schema design are key.”

---

🧠 Ultra-Short Cheat Sheet

Indexes = #1
Avoid SELECT *
Filter early
Join on indexed columns
EXPLAIN everything
Short transactions
Keyset pagination
Cache when possible

---