Database Indexing Explained Simply

A database index is a sorted lookup structure that lets the database find rows without reading every row in the table. It works like the index at the back of a book: instead of reading every page to find "invoice", you jump to the right page. Indexes make reads faster, but they cost storage and slow down writes, so the skill is adding the right ones rather than as many as possible.

The problem indexes solve

Imagine an orders table with a few million rows and this query:

SELECT id, total FROM orders WHERE customer_id = 4821;

With no index on customer_id, the database has to examine every row to see which ones match. This is called a full table scan. It is fine for a hundred rows and painful for millions. With an index, the database goes straight to the entries for customer 4821 and reads only those rows.

How an index works, simply

Most relational databases use a structure called a B-tree. Think of it as a sorted, layered directory. The top layer tells you which branch to follow, the next layer narrows it further, and after a few steps you reach the exact entry, which points to the row. Because the tree stays shallow even for very large tables, finding a value takes only a handful of steps.

The index is stored separately from the table and kept in sync automatically. That is the catch: every insert, update or delete must also update each affected index.

Creating an index

CREATE INDEX idx_orders_customer ON orders (customer_id);

The same statement works in MySQL, PostgreSQL and SQL Server. Primary keys are indexed automatically, and unique constraints create unique indexes. Foreign key columns are not always indexed for you, so check; joins on unindexed foreign keys are a classic cause of slowness.

Composite indexes and column order

An index can cover several columns. Order matters:

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);

This index helps queries that filter on customer_id, or on customer_id and created_at together. It generally does not help a query that filters only on created_at, because the index is sorted by customer first. A useful rule of thumb: put the columns you test for equality first, then the column used for ranges or sorting.

Checking whether an index is used

EXPLAIN SELECT id, total
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC;

The output differs by database, but you are looking for words such as "index" or "index scan" rather than "ALL" or "seq scan" (a sequential scan) on a large table. Remember that on a tiny table, a scan can legitimately be cheaper, and the optimiser may choose it.

When to add an index

• Columns used in WHERE filters on large tables.
• Columns used in JOIN conditions, especially foreign keys.
• Columns used in ORDER BY or GROUP BY on queries that run often.
• Columns that must be unique, such as an email address.

When not to add one

• Small tables, where a scan is already instant.
• Columns with very few distinct values, such as a yes/no flag, where an index rarely narrows the search enough.
• Tables with heavy write traffic and rare reads, where index upkeep costs more than it saves.
• Columns nobody queries. An unused index is pure overhead.

Queries that quietly ignore your index

Pattern	Why it hurts	Better
WHERE YEAR(created_at) = 2026	The function hides the column from the index	WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE name LIKE '%smith'	A leading wildcard cannot use a sorted index	Search from the start, or use a full-text index
WHERE phone = 353871234567 on a text column	Type mismatch can force conversion on every row	Compare with the matching type
SELECT * on wide tables	Pulls data the index cannot supply	Select only the columns you need

A practical checklist

1. Find the slow queries first, using the slow query log or your monitoring tool. Do not guess.
2. Run EXPLAIN on each and read the plan.
3. Add one index at a time, on a test copy of realistic data.
4. Re-run EXPLAIN and compare timing before and after.
5. Check the effect on writes if the table is busy.
6. Remove indexes that monitoring shows are never used.
7. Revisit indexing when queries or data volumes change.

Common mistakes

• Indexing everything. Writes slow down and storage balloons.
• Duplicate indexes. An index on (customer_id) is redundant if you already have (customer_id, created_at) serving the same queries.
• Wrong column order in composite indexes.
• Adding indexes on production without testing. On large tables, building an index can lock or load the system; schedule it carefully.
• Treating indexes as a substitute for good queries. Fetching far more rows than needed is still slow with an index.

Reading an execution plan without fear

An execution plan is the database's explanation of how it intends to answer a query. You do not need to understand every line. Look for four things: whether it reads the whole table, which index it picks, how many rows it expects to examine, and whether it sorts a large result in memory or on disk. In MySQL, adding the EXPLAIN keyword in front of the query shows this. PostgreSQL offers EXPLAIN and EXPLAIN ANALYZE, which actually runs the query and shows real timings, so be careful using it with statements that change data. SQL Server users can view the estimated or actual plan in management tools.

Covering indexes

If an index contains every column a query needs, the database can answer from the index alone without touching the table. This is called a covering index. For example, a query that lists order totals by customer might use:

CREATE INDEX idx_orders_cover ON orders (customer_id, created_at, total);

This is a powerful technique for a few critical queries, but it also makes the index larger. Use it selectively, after measuring, rather than as a default.

Indexes and writes: the trade-off

Every extra index means more work on each insert, update and delete, because the database has to maintain all of them. On a table that receives constant writes, such as a log or an event stream, think hard before adding indexes. On a table that is read far more than it is written, such as products or customers, generous indexing is usually worth it. Neither situation is universal, so base decisions on how your application behaves.

Index maintenance

Over time, indexes can become fragmented or bloated as rows are modified and deleted, and the database's statistics about your data can go stale, leading it to pick poor plans. Most systems handle much of this automatically, but a scheduled maintenance routine is still sensible: update statistics, rebuild or reorganise indexes where the tooling for your engine recommends it, and review unused indexes. Follow your database vendor's current guidance rather than a fixed recipe, because behaviour differs between engines and versions.

Indexes in MongoDB and other NoSQL databases

The same idea applies in document databases. MongoDB, for instance, supports indexes on fields inside documents, and queries without a suitable index scan the whole collection. The same rules hold: index what you filter and sort on, test with the query planner and avoid indexing everything.

When it is worth getting help

Most performance problems in business applications come down to a few queries and a few missing indexes, so a focused review often pays off. Soch Tech builds applications on MySQL, SQL Server, Oracle and MongoDB as part of its database and application development service, with performance considered from the schema design onwards.