Database Quiz

Most everyday database trouble comes from a handful of ideas: what indexes cost, what a transaction promises, how tables are joined and designed, and what happens when two writers want the same rows. Get those right and the rest of SQL becomes much easier to reason about.

Indexes: faster reads, slower writes

An index is a separate, sorted structure that points back to the rows in a table. Without one, finding every order for customer 42 means reading the whole table row by row, a 'full table scan'. With an index on customer_id, the database jumps straight to the matching entries, the way you use the index at the back of a book instead of reading every page.

CREATE INDEX idx_orders_customer
  ON orders (customer_id);
 
SELECT * FROM orders
WHERE customer_id = 42;

That is what an index is for: reading. It doesn't make the table smaller (it takes extra storage of its own), and it does nothing about network latency between your app and the database.

The write cost

Every index is a copy of some of the table's data, and it has to be kept in step with the table. Each INSERT adds an entry to every index on the table, each DELETE removes one, and an UPDATE to an indexed column moves one. A table with ten indexes does ten extra pieces of work on every insert. Indexes also take disk space and memory, competing with the data itself for the cache.

So indexes aren't free. Index the columns your queries actually filter, join and sort on, and drop the ones nothing uses. On a write-heavy table, such as an event log, a few well-chosen indexes beat one for every column.

B-tree and hash indexes

Most relational databases build a B-tree index unless you ask for something else. A B-tree keeps its keys in sorted order in a shallow, balanced tree, so the database can find one value in a few steps and then walk along the leaves in order. That makes it good at exact matches and at ranges:

SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';

A hash index files each key by its hash. That is quick for an 'equals' lookup, but no help for a range: neighbouring dates hash to unrelated places, so there is no order to walk. Bitmap indexes suit columns with only a few distinct values, such as a status flag, in analytics workloads, and full-text indexes find words inside text. For 'between', 'greater than' and ORDER BY, a B-tree is the one you want.

The WHERE clause is your safety catch

DELETE and UPDATE change every row their WHERE clause matches. Leave the WHERE clause out, and they match every row in the table.

-- Deletes one customer
DELETE FROM customers WHERE id = 42;
 
-- Deletes every customer
DELETE FROM customers;

The second statement is valid SQL, so the database doesn't refuse it, and it doesn't stop after the first row. It empties the table. The table itself stays, with its columns and indexes: removing the table is DROP TABLE, a different command.

Habits that stop this happening:

• Write a SELECT with the same WHERE clause first, check the rows it returns, then turn it into the DELETE.
• Run risky changes by hand inside a transaction, check the row count, and only then COMMIT.
• Turn on your client's guard where it has one. MySQL's safe-updates mode, for example, refuses an UPDATE or DELETE without a key in the WHERE clause or a LIMIT.

Transactions and ACID

A transaction groups several statements into one unit of work. The classic example is a bank transfer: take 50 from one account and add 50 to another. ACID names the four promises a transaction makes:

• Atomicity: all of it happens, or none of it does. If the second update fails, the first is undone.
• Consistency: each transaction moves the database from one valid state to another, keeping its rules, such as a CHECK on balances or a foreign key to a real customer.
• Isolation: transactions running at the same time don't see each other's half-finished work.
• Durability: once the database says the commit worked, the change survives a crash or a power cut.

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

The 'consistency' here is about rules inside one database. It is a different idea from the consistency in the CAP theorem, which is about copies of data on several servers agreeing.

How durability works

Durability is the property that keeps your data correct after a crash. Databases usually get it from a write-ahead log: before a commit is confirmed, the change is written to a log on disk and flushed. If the server dies a second later, it replays the log on restart, and the committed change is still there. Work that was never committed is rolled back, which is atomicity playing its part.

Some neighbouring ideas sound as if they might help, but do other jobs. Indexing is about finding data fast, normalisation is about table design, and sharding splits data across servers so it can grow. None of them protects a commit from a crash.

Deadlocks

Isolation needs locks. When a transaction updates a row, it holds a lock on that row until it commits, and any other transaction that wants to change the same row waits.

A deadlock is two transactions each holding a lock the other needs. Neither can carry on, and neither will let go. Here is how one forms, and how the database gets out of it:

The rolled-back transaction gets an error, and your application should retry it. Slow disks, missing keys or a pile of indexes can make queries slow and lock waits longer, but slowness is not a deadlock: the cause is always the circular wait.

To make deadlocks rarer:

• Lock rows and tables in the same order in every transaction. If both had gone for row A before row B, the second would simply have waited its turn.
• Keep transactions short, and never wait for user input inside one.
• Treat a deadlock error as something to retry, not a crash.

Joins

A JOIN combines rows from two or more tables into one result, matching them on a condition, usually a foreign key.

SELECT c.name, o.total
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id;

It doesn't create a new table: the result exists only for that query. It isn't a performance feature, and it has nothing to do with encryption.

Why big joins are expensive

Finding one row through an index is cheap: a few steps down a B-tree. A single INSERT, or an UPDATE that finds its row through an index, touches a handful of pages. A join between two large tables may have to match millions of rows against millions of others. The database picks a strategy, such as looking each row up through an index, building a hash table in memory, or merging two sorted inputs, but with big inputs and no useful index it may still read both tables in full and spill to disk.

That is why a slow query is so often a join. Index the join columns, filter early so fewer rows reach the join, and check the query plan with EXPLAIN.

Normalisation

Normalisation is designing your tables so each fact is stored once. Instead of copying the customer's name and email onto every order, you keep them in a customers table and store only customer_id on each order.

The main goal is less duplicated data, and with it fewer ways for the data to disagree: change an email once and every order sees it, instead of hunting down 300 copies. It isn't mainly about speed. A normalised design often needs more joins to read, so some reads get slower, which is why reporting databases sometimes denormalise on purpose. Nor does it make the database more available, or encrypt anything.

Common mistakes

• Adding an index on every column 'just in case', then wondering why inserts crawl.
• Running an UPDATE or DELETE by hand without checking its WHERE clause first.
• Querying a column by range with only a hash index on it, or with no index at all.
• Long transactions that hold locks while they wait for something else.
• Treating normalisation as a speed trick instead of a way to keep data consistent.

Key takeaways

• Indexes speed up reads and slow down writes, so index what your queries use.
• B-tree indexes handle ranges; hash indexes only handle exact matches.
• A DELETE or UPDATE without WHERE changes every row.
• Durability, the D in ACID, means a committed change survives a crash.
• A deadlock is two transactions waiting on each other: lock in a consistent order and retry.