SQL
Query Optimisation and Pagination Performance
A practical checklist for making a slow query fast, and the specific fix for deep pagination that no amount of indexing solves.
#SQL
Notes related to SQL.
131 notes tagged
SQL
Scans, Seeks, Join Strategies and Sorting
What the engine actually does: full scans versus index seeks, nested loop and hash joins, and why a sort or a temporary table appears in your plan.
SQL
How a Query Executes and Reading EXPLAIN
What happens between typing a query and getting rows, and how to read an execution plan - the single most useful performance skill in SQL.
SQL
When Indexes Help and When They Hurt
Indexes are not free. Learn the cases where the optimiser correctly ignores them, how to find unused and duplicate indexes, and how to maintain them.
SQL
Composite, Unique and Covering Indexes
Multi column indexes and why their order decides everything: the leftmost prefix rule, selectivity, covering indexes and index only scans.
SQL
What Indexes Are and Why They Exist
An index is a sorted lookup structure the database maintains for you. Learn how a B-tree finds a row, what an index costs, and the clustered index in...
SQL
Deadlocks and Lock Contention
Why two transactions can wait for each other forever, how the database resolves it, and the design habits that prevent deadlocks and reduce contention...
SQL
Locks: Shared, Exclusive, Row and Table Level
How the database stops two transactions corrupting each other: lock modes, granularity, locking reads, and why an unindexed WHERE clause can lock a wh...
SQL
Isolation Levels and Read Phenomena
The four isolation levels, the three anomalies they prevent, and what each database actually defaults to - with the session by session demonstrations.
SQL
ACID Properties Explained
Atomicity, consistency, isolation and durability - what each guarantee actually promises, how the database delivers it, and where each one can be weak...
SQL
BEGIN, COMMIT, ROLLBACK and Savepoints
Grouping statements into one unit of work that either fully applies or fully undoes. Learn transaction boundaries, savepoints, autocommit and what sil...
SQL
Temporary Tables vs CTEs vs Views vs Derived Tables
Four ways to name an intermediate result, and a decision rule for choosing between them based on scope, reuse and whether you need an index.