Advanced SQL: indexes, plans and transactions

Understanding the optimizer, reading an execution plan and choosing the right isolation level: the Codara SQL bank covers the decisions that take a query from 100 ms to 1 ms.

Indexing: beyond the simple index

A composite index is a tree where each level follows a column order. Equality columns must precede range columns, otherwise the optimizer can only use the prefix. A covering index (all needed columns) enables an index-only scan and avoids table lookups. Beware the write cost: each additional index slows down INSERT and UPDATE.

-- Lookup: equality on tenant_id, range on created_at
CREATE INDEX idx_quiz_tenant_created
    ON quiz_session (tenant_id, created_at);

-- The optimizer can cover: no table reads
SELECT id, score
FROM quiz_session
WHERE tenant_id = 42 AND created_at >= '2026-08-01';

Reading and fixing execution plans

The execution plan translates your query into operations: indexed access, sort, joins, temporary tables. Cost indicators are `rows` (estimate), access `type` and the `Extra` column. `Using filesort` is not always a disk sort: it means a sort that cannot use index order. `EXPLAIN ANALYZE` measures real costs and lets you verify a fix.

EXPLAIN ANALYZE
SELECT q.category, COUNT(*) AS attempts, AVG(qs.score) AS avg_score
FROM quiz_session qs
JOIN question q ON q.id = qs.question_id
WHERE qs.tenant_id = 42
GROUP BY q.category
ORDER BY attempts DESC;

Transactions, locks and isolation levels

A transaction guarantees atomicity and isolation, but each isolation level has a lock price. READ COMMITTED locks written rows; REPEATABLE READ adds snapshot consistency; SERIALIZABLE locks ranges and can block concurrent queries. Deadlocks shrink by locking resources in a constant global order and keeping transactions short.

START TRANSACTION;

-- Lock the row before deciding
SELECT id, remaining_slots
FROM exam_session
WHERE id = 10 FOR UPDATE;

UPDATE exam_session SET remaining_slots = remaining_slots - 1 WHERE id = 10;

COMMIT;

Window functions and advanced aggregates

Window functions (ROW_NUMBER, RANK, LAG, SUM OVER) compute values relative to a group without losing detail rows, unlike GROUP BY. They elegantly replace self-joins for rankings, differences between consecutive rows and running totals. The partition and order of the OVER clause define the frame; a wrong frame produces results that are syntactically correct but semantically wrong.

SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY theme ORDER BY score DESC) AS rank_in_theme,
    LAG(score) OVER (PARTITION BY user_id ORDER BY taken_at) AS previous_score
FROM quiz_result;