Indexes, Transactions & Performance
Make queries fast with indexes, protect data integrity with transactions, and understand how query planners work — critical for building reliable, performant security tools.
Learning Objectives
- → Create and use indexes to speed up queries
- → Understand B-tree index internals
- → Use EXPLAIN QUERY PLAN to analyze queries
- → Wrap multi-step operations in ACID transactions
- → Handle transaction conflicts and deadlocks
What is an Index?
An index is a separate data structure that maps column values to row locations — like a book index.
Without index: scan every row (O(n))
With B-tree index: binary search (O(log n))
-- Create an index
CREATE INDEX idx_scans_user_id ON scans(user_id);
CREATE INDEX idx_scans_host ON scans(host);
CREATE INDEX idx_scans_status ON scans(status);
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_scans_user_status ON scans(user_id, status);
-- Drop an index
DROP INDEX idx_scans_status;
-- List indexes (SQLite)
.indexes scans
SELECT * FROM sqlite_master WHERE type='index';
B-Tree Index Internals
B-tree on scans.user_id:
[5]
/ \
[2,4] [7,9]
/ | \ / \
[1][3][4][6][8]
rows at leaves → table row pointers
Index scan: find value in tree → jump directly to rows
Full table scan: read every row in order
When indexes help:
- WHERE col = value (equality)
- WHERE col > value (range)
- ORDER BY col (avoids sort)
- JOIN ON col (join condition)
When indexes DON'T help:
- WHERE UPPER(email) = 'ALICE' (function on indexed column)
- WHERE status != 'closed' (low selectivity, many matches)
- Very small tables (full scan is faster)
Composite Index Column Order
-- Index: (user_id, status)
CREATE INDEX idx_user_status ON scans(user_id, status);
-- Uses index: both columns, leading column first
WHERE user_id = 1 AND status = 'open' ✓
WHERE user_id = 1 ✓ (partial use)
-- Does NOT use index: skips leading column
WHERE status = 'open' ✗ (must scan index)
EXPLAIN QUERY PLAN
-- See how SQLite executes a query
EXPLAIN QUERY PLAN
SELECT s.host, COUNT(*) as port_count
FROM scans s
JOIN ports p ON p.scan_id = s.id
WHERE s.user_id = 1 AND s.status = 'complete'
GROUP BY s.host;
-- Output indicates:
-- SCAN TABLE scans → full table scan (bad, add index)
-- SEARCH TABLE scans → index used (good)
-- USE TEMP B-TREE → sort needed (consider index on ORDER BY col)
Transactions and ACID
ACID properties guarantee reliability:
| Property | Meaning |
|---|---|
| Atomicity | All operations succeed or all are rolled back |
| Consistency | DB stays valid before and after |
| Isolation | Concurrent transactions don't see each other's partial work |
| Durability | Committed data survives crashes |
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- On error: rollback
BEGIN;
UPDATE scans SET status = 'processing' WHERE id = 42;
-- if something fails...
ROLLBACK; -- undo everything since BEGIN
Savepoints
BEGIN;
INSERT INTO scans (host, user_id) VALUES ('10.0.0.1', 1);
SAVEPOINT after_insert;
UPDATE hosts SET last_seen = CURRENT_TIMESTAMP WHERE ip = '10.0.0.1';
-- this fails...
ROLLBACK TO after_insert; -- undo update, keep insert
COMMIT; -- only the insert is saved
Transactions in Application Code
import sqlite3
conn = sqlite3.connect("cyberlearn.db")
try:
conn.execute("BEGIN")
conn.execute(
"INSERT INTO scans (host, user_id, status) VALUES (?, ?, ?)",
("10.0.0.1", 1, "pending")
)
scan_id = conn.execute("SELECT last_insert_rowid()").fetchone()[0]
conn.execute(
"INSERT INTO audit_log (table_name, record_id, action) VALUES (?, ?, ?)",
("scans", scan_id, "INSERT")
)
conn.execute("COMMIT")
return scan_id
except Exception as e:
conn.execute("ROLLBACK")
raise
Query Performance Patterns
-- BAD: function prevents index use
WHERE LOWER(username) = 'alice'
-- GOOD: store lowercase, or use COLLATE NOCASE
WHERE username = 'alice' COLLATE NOCASE
-- or: CREATE INDEX idx_lower_user ON users(LOWER(username))
-- BAD: SELECT * fetches all columns
SELECT * FROM scans WHERE user_id = 1;
-- GOOD: covering index — all needed columns in the index
CREATE INDEX idx_scan_cover ON scans(user_id, status, host, created_at);
SELECT status, host, created_at FROM scans WHERE user_id = 1;
-- SQLite satisfies query entirely from index, never touches table
-- BAD: N+1 query problem
for user in users:
scans = db.query("SELECT * FROM scans WHERE user_id = ?", user.id)
-- GOOD: single JOIN
SELECT u.username, s.host FROM users u JOIN scans s ON s.user_id = u.id;
SQLite Write-Ahead Logging
-- Enable WAL mode for better concurrent performance
PRAGMA journal_mode = WAL;
-- Other useful pragmas
PRAGMA foreign_keys = ON; -- enforce FK constraints (off by default!)
PRAGMA cache_size = -64000; -- 64 MB cache
PRAGMA synchronous = NORMAL; -- faster writes, still safe
Run EXPLAIN QUERY PLAN on 5 different queries against your scan database (without indexes). Note which ones do full table scans. Add appropriate indexes. Re-run EXPLAIN and verify they now use SEARCH instead of SCAN.
What does EXPLAIN QUERY PLAN output tell you?
What does enabling PRAGMA foreign_keys = ON do in SQLite?
Write a Python function create_scan(host, user_id, ports) that: (1) inserts a scan row, (2) inserts all port rows, (3) inserts an audit log entry — all in one transaction. If any step fails, rollback everything.
What does ROLLBACK do?
What does Atomicity in ACID mean?
Given Python code that loops over users and issues a separate DB query per user to count scans — rewrite it as a single SQL query with GROUP BY. Benchmark both versions on 100 users.
What is the N+1 query problem?