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.
#Performance
Making queries and schemas faster.
13 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
Temporary Tables and Session Scope
A real table that exists only for your session. Learn CREATE TEMPORARY TABLE, its scope and lifetime, indexing it, and the dialect differences.
SQL
Trigger Use Cases and Limitations
What triggers are genuinely good at, the costs they impose, and the honest list of reasons teams end up removing them.
SQL
User Defined Functions
Functions return a value and can be used inside a query. Learn CREATE FUNCTION, determinism, the difference from procedures, and the performance trap.
SQL
Anomalies and Denormalisation
The three anomalies normalisation prevents, and the disciplined way to reintroduce redundancy when reads genuinely demand it.
SQL
Subqueries in SELECT, FROM and WHERE: Join vs Subquery vs EXISTS
The same question answered four ways, with a straight comparison of readability, NULL safety and cost - and a rule for choosing between them.