SQL, Indexing & Isolation

Index types

Type	Good for	Notes
B-tree (default)	=, <, >, BETWEEN, ORDER BY, prefix LIKE 'abc%'	balanced, sorted; InnoDB and PostgreSQL default
Hash	equality only	PostgreSQL USING hash (WAL-logged since 10); InnoDB adaptive hash is internal
Composite	multi-column filters and sorts	column order matters
Unique	enforce uniqueness	allows multiple NULLs in PostgreSQL/MySQL, not SQL Server
Covering	index-only scan	PostgreSQL INCLUDE (cols) (11+); MySQL: put columns in the index
Partial	subset of rows	PostgreSQL WHERE status = 'ACTIVE'
Expression / functional	lower(email) lookups	PostgreSQL expression index; MySQL 8.0.13+ functional index
GIN / GiST	JSONB, arrays, full text / geometric, range	PostgreSQL
Clustered	table rows stored in PK order	InnoDB always (PK); PostgreSQL heap is unclustered (CLUSTER is one-time)
Full-text	word search	tsvector / FULLTEXT

• Secondary indexes in InnoDB store the PK; a wide PK bloats every index. Random UUIDv4 PKs cause page splits; prefer sequential ids (UUIDv7 / ULID).
• Every index costs write amplification and space; drop unused ones (pg_stat_user_indexes.idx_scan = 0).
• Low-cardinality columns (boolean, status) alone are poor index candidates unless partial or composite.
• Foreign key columns need an index in PostgreSQL (not automatic); InnoDB creates one automatically.

Composite index rules

• Leftmost-prefix: index (a, b, c) serves a, a,b, a,b,c; not b or c alone (PostgreSQL may skip-scan poorly; MySQL 8.0.13+ has limited skip scan; PostgreSQL 18 adds skip scan).
• Order columns: equality columns first, then the range column, then sort columns (ESR-style: Equality, Sort, Range).
• Columns after the first range predicate cannot narrow the scan.
• ORDER BY a, b can use (a, b) and avoids a sort; mixed ASC/DESC needs a matching index definition.
• Higher selectivity first is a tie-breaker only after the rules above.

-- query
SELECT id FROM orders WHERE user_id = ? AND status = 'PAID' AND created_at > ? ORDER BY created_at DESC LIMIT 20;
-- good index
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

Queries that bypass indexes

Pattern	Why	Fix
WHERE lower(email) = ?	function on column	expression index or store normalized
WHERE col + 1 = 5	arithmetic on column	move to the constant side
LIKE '%abc'	no leading prefix	trigram (pg_trgm) / full-text
WHERE varchar_col = 123	implicit cast	match types
OR across different columns	planner may not use one index	UNION ALL or indexes on both (bitmap OR)
!=, NOT IN	low selectivity	rewrite, NOT EXISTS
Large OFFSET	scans and discards	keyset pagination: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20
Stale statistics	bad row estimates	ANALYZE (Postgres) / ANALYZE TABLE (MySQL)
SELECT *	defeats covering index	select needed columns

Reading EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;     -- PostgreSQL: executes the query
EXPLAIN ANALYZE SELECT ...;                -- MySQL 8.0.18+
EXPLAIN FORMAT=TREE SELECT ...;            -- MySQL

PostgreSQL node	Meaning
Seq Scan	full table read; fine for small tables or most rows
Index Scan	index lookups + heap fetch per row
Index Only Scan	no heap fetch (needs visibility map; Heap Fetches should be low)
Bitmap Index/Heap Scan	many matches; combines indexes
Nested Loop / Hash Join / Merge Join	small outer + indexed inner / large unsorted / sorted inputs
Sort (external merge Disk)	spilled work_mem

• Compare rows= estimate vs actual rows=: a big gap means stale stats or correlated columns.
• cost is in arbitrary units; actual time in ms (per loop; multiply by loops).
• Watch Rows Removed by Filter (index not selective) and Buffers: shared read (cold I/O).
• MySQL type column best to worst: system, const, eq_ref, ref, range, index, ALL. Extra: Using index (covering) good; Using filesort, Using temporary suspicious.

Isolation levels vs anomalies

Level	Dirty read	Non-repeatable read	Phantom	Lost update / write skew
READ UNCOMMITTED	possible	possible	possible	possible
READ COMMITTED	no	possible	possible	possible
REPEATABLE READ	no	no	possible (SQL standard)	write skew possible
SERIALIZABLE	no	no	no	no

• PostgreSQL: default READ COMMITTED; REPEATABLE READ is snapshot isolation (no phantoms, but write skew); SERIALIZABLE is SSI and may abort with 40001 (retry). READ UNCOMMITTED behaves as READ COMMITTED.
• MySQL InnoDB: default REPEATABLE READ; MVCC snapshot for plain reads, gap/next-key locks block phantoms for locking reads; SELECT ... FOR UPDATE reads latest committed data, not the snapshot.
• Oracle and SQL Server default to READ COMMITTED (SQL Server optionally with snapshot).
• Lost update fix: UPDATE t SET n = n + 1, optimistic version column, or SELECT ... FOR UPDATE.

-- Spring / JDBC equivalent: @Transactional(isolation = Isolation.SERIALIZABLE)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Locking reads

Statement	Effect
SELECT ... FOR UPDATE	exclusive row locks; blocks other writers and locking readers
SELECT ... FOR SHARE (PG, MySQL 8) / LOCK IN SHARE MODE	shared locks; blocks writers
... NOWAIT	error immediately if locked
... SKIP LOCKED	skip locked rows (work queues; PG 9.5+, MySQL 8.0+)
LOCK TABLE	table level, rarely correct

SELECT id FROM jobs WHERE status = 'NEW' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;

• Locks live until commit/rollback; keep the transaction short.
• InnoDB locks index records; a locking read without a usable index can lock every scanned row/gap (looks like a table lock).
• Deadlocks: two transactions lock rows in opposite order. DB aborts one (PG 40P01, MySQL 1213); retry. Fix by consistent lock order.
• Advisory locks (PG pg_advisory_xact_lock) / MySQL GET_LOCK for app-level mutexes.

Common slow-query causes

• Missing or wrong-order composite index; low-selectivity leading column.
• N+1 queries from the ORM (one query per row).
• OFFSET pagination deep into a large table.
• Functions/casts on indexed columns; leading wildcard LIKE.
• SELECT * with wide rows / TEXT/JSON columns.
• Sorting/grouping without an index (disk sort; raise work_mem carefully).
• Stale statistics, bloated tables (PG VACUUM), too many dead tuples.
• Long transactions holding locks or old snapshots (blocks vacuum / undo purge).
• Lock contention on hot rows (counters); shard counters or use queue.
• Missing connection pooling; pool too large (rule of thumb: cores * 2 + spindles).
• COUNT(*) on large tables (PG full scan; use estimates or counters).
• Joins on unindexed or mismatched-type columns; huge IN (...) lists.
• Find them: PG pg_stat_statements, auto_explain; MySQL slow query log (long_query_time), performance_schema, pt-query-digest.

Handy SQL

SELECT dept, COUNT(*) c FROM emp GROUP BY dept HAVING COUNT(*) > 5;           -- HAVING filters groups
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) rn FROM emp) t WHERE rn <= 3;
SELECT sal FROM emp ORDER BY sal DESC OFFSET 1 LIMIT 1;                        -- 2nd highest (PG)
SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL;            -- anti-join
INSERT INTO t (k, v) VALUES (1, 'x') ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;   -- PG upsert
INSERT INTO t (k, v) VALUES (1, 'x') ON DUPLICATE KEY UPDATE v = VALUES(v);          -- MySQL upsert

• Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
• NULL = NULL is unknown; use IS NULL; NOT IN with a NULL in the subquery returns no rows.