SQL Indexes, Query Performance & ACID Transactions

8 min read SQL 2 views

Writing queries that work accurately on 50 sample rows is only half of database engineering. When tables grow to hundreds of thousands of users and millions of progress logs, knowing how B-Tree indexes speed up reads and how ACID transactions safeguard concurrent updates keeps production applications fast and bug-free.

1. How Database Indexes Accelerate Queries

Without an index on a filtered column like slug or email, the database performs a full table scan, inspecting every single row from start to finish. Adding a B-Tree index builds a sorted lookup structure (much like a book's index) so the engine locates matching rows in logarithmic O(log N) time:

-- Speed up course/lesson lookups and user dashboard queries
CREATE UNIQUE INDEX idx_courses_slug ON courses (slug);
CREATE INDEX idx_progress_user_course ON lesson_progress (user_id, course_id);

Composite index column order matters: An index on (user_id, course_id) accelerates queries filtering by user_id alone OR both user_id and course_id, because keys are sorted left-to-right.
Avoid over-indexing write-heavy tables: Every index speeds up SELECT reads while adding small overhead to INSERT, UPDATE, and DELETE writes since indexes must stay synchronized.
Inspect execution plans with EXPLAIN: Prefix any slow SELECT query with EXPLAIN in MySQL or SQLite to confirm whether it uses an index rather than performing a full scan.

2. Protecting Multi-Step Operations with Transactions

Imagine transferring ₹500 between two bank accounts—subtracting from Account A and adding to Account B. If the server crashes between those two UPDATE statements without a transaction, money vanishes! Wrapping both operations in a transaction forces an all-or-nothing guarantee:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1 AND balance >= 500;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;   -- Persist both changes together (or ROLLBACK to undo all)

3. The Four ACID Guarantees

Property	Meaning in Plain Language
Atomicity	All SQL statements inside a transaction succeed together or roll back cleanly together.
Consistency	Transactions leave foreign keys, unique indexes, and constraints valid before and after commit.
Isolation	Concurrent sessions cannot read half-completed intermediate mutations from another transaction.
Durability	Once `COMMIT` returns success, changes are flushed to non-volatile storage and survive power loss.

4. Why Indexes Sometimes Stop Working (Sargable Queries)

Creating an index is not enough if a query is written in a way that prevents the optimizer from reading the B-Tree index cleanly ("non-sargable" predicates). Wrapping an indexed column inside a function like WHERE LOWER(email) = 'alex@example.com' or searching with a leading wildcard LIKE '%python' forces a slow table scan instead of an index lookup. Store normalized values on write or compare raw columns cleanly (WHERE email = 'alex@example.com').

5. Safe Parameterized Queries & Race-Condition Prevention

When checking balances or decrementing limited inventory under concurrent web requests, reading a balance in PHP and checking it in an if statement risks race conditions when two requests arrive in the same millisecond. Guard critical balance updates either with atomic conditional predicates (WHERE balance >= 500) or pessimistic row locks inside transactions (SELECT balance FROM accounts WHERE id = 1 FOR UPDATE in InnoDB/MySQL).

Above all, never build SQL strings using string concatenation with user inputs. Always pass inputs through prepared statements ($pdo->prepare(...)->execute([$param])), which both eliminates SQL injection vulnerabilities and allows the database engine to reuse compiled query plans cleanly.

6. Hands-On Transaction Rollback Practice & Key Takeaways

Run the SQLite transaction snippet below, add a third transaction step simulating a failing transfer, change COMMIT to ROLLBACK, and confirm via the final SELECT statement that both account balances stay cleanly untouched.

Index hot filter, join, and sort columns deliberately: Composite B-Tree indexes ordered left-to-right alongside sargable comparisons avoid full table scans as traffic and dataset sizes grow.
Use START TRANSACTION / COMMIT for linked writes: Multi-step financial updates, order checkouts, and inventory decrements belong inside atomic ACID transactions so crashes never persist half-finished state.
Always parameterize user values via prepared statements: Treating untrusted input strictly as bound query parameters protects applications reliably against SQL injection exploits.

sql Example

Run Code

CREATE TABLE accounts (id INT PRIMARY KEY, owner TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Riya', 1000), (2, 'Aman', 500);

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

SELECT owner, balance FROM accounts;

SQL Indexes, Query Performance & ACID Transactions