Learning SQL in 20 Hours — Day 18: Index

Indexes & SQL Performance Basics

Up until now, my focus has been on:

• writing correct queries
• modeling data properly
• expressing business logic

But correctness alone isn’t enough in real systems. On Day 18, I learned how SQL queries run faster with indexes — and when they don’t.

Indexes are one of the most important (and misunderstood) performance tools in SQL.

⚙️ Execution Setup

Environment:

• SQLite on macOS
• Table used: expenses_main

.headers on
.mode box

🧠 What I Learned

[embed]

📌 Indexes improve SELECT, not INSERT speed.

🛠️ Indexes in Action

1️⃣ Query Without Index (Baseline)

.print ▶ Query without index
EXPLAIN QUERY PLAN
SELECT *
FROM expenses_main
WHERE category = 'Food';

You’ll see something like:

SCAN TABLE expenses_main

This means SQLite scanned every row.

2️⃣ Create a Simple Index

.print ▶ Create index on category
CREATE INDEX idx_expenses_category
ON expenses_main(category);

3️⃣ Query With Index

.print ▶ Query with index
EXPLAIN QUERY PLAN
SELECT *
FROM expenses_main
WHERE category = 'Food';

Now SQLite uses:

SEARCH TABLE expenses_main USING INDEX idx_expenses_category

🚀 Faster lookup.

4️⃣ Composite Index (Multiple Columns)

.print ▶ Composite index on (type, category)
CREATE INDEX idx_type_category
ON expenses_main(type, category);
EXPLAIN QUERY PLAN
SELECT *
FROM expenses_main
WHERE type='Expense'
  AND category='Food';

📌 Column order matters in composite indexes.

5️⃣ Index for ORDER BY

.print ▶ Index for sorting
CREATE INDEX idx_date
ON expenses_main(date);
EXPLAIN QUERY PLAN
SELECT *
FROM expenses_main
ORDER BY date;

6️⃣ Index Trade-off (Write Cost)

Indexes speed reads but slow writes:

.print ▶ Insert still works, but slightly slower
INSERT INTO expenses_main
(date, category, description, amount, type)
VALUES
('2025-09-18','Food','Indexed insert test',250,'Expense');

📌 In high-write systems, index carefully.

7️⃣ Dropping an Index

.print ▶ Drop index
DROP INDEX IF EXISTS idx_date;

📂 GitHub Repo

👉 Day 18 — Indexes & Performance https://github.com/kbrepository/learning-sql-fundamentals/tree/main/day18_indexes

Folder Structure

day18_indexes/
├── 00_prep.sql
├── 01_no_index_plan.sql
├── 02_create_index.sql
├── 03_query_with_index.sql
├── 04_composite_index.sql
├── 05_order_by_index.sql
└── 06_drop_index.sql

🪄 Execution Steps

sqlite3 expenses.db

Then:

.read day18_indexes/00_prep.sql
.read day18_indexes/01_no_index_plan.sql
.read day18_indexes/02_create_index.sql
.read day18_indexes/03_query_with_index.sql
.read day18_indexes/04_composite_index.sql
.read day18_indexes/05_order_by_index.sql
.read day18_indexes/06_drop_index.sql

🎯 Reflection After Day 18

✅ Learned why queries are slow ✅ Understood how indexes work internally ✅ Can read query plans ✅ Performance thinking unlocked

This day connects SQL knowledge to real production behavior.