Adyakshar Labs

Adyakshar Labs / Blog

Engineering

A Database Index Is a Shelf Mark: The Library Idea Behind Fast Queries

Illustration of two shelves of colourful book spines with one highlighted, next to a shelf-mark card, titled A database index is a shelf mark

Picture an old library with a few thousand books and no catalogue. You ask for one title. The only way to find it is to walk the shelves and read every spine until you get lucky.

Now picture the same library where every book has a shelf mark: a letter and a number on the spine, and a card that says which shelf holds which mark. You look up the card, walk to shelf K, and take the book that carries the number 26. A few steps, not a few hours.

That is a database index. It is one of the most useful ideas in software, and it is easy to explain once you stop thinking about software.

What the database does without an index

A table in a database is a long row of records. When you ask for the customer whose email is a particular address, a database with no index has one option: start at the first row and check each one. Engineers call this a full scan.

A full scan is fine for a few hundred rows. It is slow for a few million, and it gets slower in step with the size of the table. The query that felt instant during development becomes the page that takes eight seconds in production, and nothing in the code changed. Only the amount of data did.

What an index adds

An index is a second, smaller structure kept alongside the table. It holds the values of one column in sorted order, and for each value it remembers where the full record lives. That is the card catalogue.

Because the entries are sorted, the database does not scan. The simplest way to see the saving is binary search: look at the middle entry, decide which half your value must be in, discard the other half, and repeat. A sorted list of 4,096 entries needs at most 13 looks. Twelve halvings take 4,096 candidates down to one, because 2 multiplied by itself 12 times is 4,096, and one more look confirms that the last entry is the one you want. Doubling the table adds only one more look.

Real databases usually store the index as a B-tree, which is a wider version of the same idea. Each node holds many entries instead of one, so each step rules out far more than half, and a table with millions of rows is typically reached in three or four steps. The principle is the same: sorted entries let you skip almost everything.

Two panels comparing the work: reading every book in order against halving a sorted list from 4,096 candidates to one, found by the 13th check.

The same 4,096 books, searched two ways. The count of 13 is the worst case for a plain sorted list.

The part people forget: filing costs something

In a library, a new book is not finished when it arrives. Someone has to give it a shelf mark, put it in the right place and add a card to the catalogue. If the library keeps three catalogues, one by title, one by author and one by subject, then every new book needs three cards.

Databases behave the same way. Every index on a table must be updated whenever a row is added, changed or removed. So:

  • Indexes make reading faster.
  • Indexes make writing slower.
  • Indexes take up space, because the catalogue is an extra thing to store.

This is why the advice is never simply to index everything. A table with a dozen indexes can be quick to search and slow to write to, and the slow writes often show up at the worst moment, when traffic is highest.

How to decide which indexes to keep

You do not need a theory for this. You need a short list of questions.

  1. What do you search by most? The columns that appear in your most frequent and most important lookups are the first candidates. A login lookup by email, an order lookup by customer, a report filtered by date.
  2. How much does the table change? A table that is mostly read, such as a product catalogue, can carry more indexes. A table that receives constant writes, such as an event log, should carry few.
  3. Is the column specific enough? An index on a column with only two possible values, such as yes and no, narrows very little. It is like a shelf mark that says every book is on shelf K.
  4. Is the index still used? Many databases can show how a query is run. Look at the plan for your slowest queries and check whether the index is chosen. An index nobody uses is cost with no benefit.

A small habit that pays off

When a page or report is slow, do not guess. Take the exact query, ask the database how it plans to run it, and look for the words that mean a full scan. If you find one on a large table, you have found your first candidate. Add one index, measure again, and keep it only if the improvement is real.

Treat indexes as a decision with a price on both sides, like a library choosing which catalogues to keep. The right set is the one that matches the questions your readers actually ask.

In short

An index is a shelf mark. It lets the database walk straight to the right place instead of reading everything. The price is a little extra work every time something is filed. Index what you search most, leave the rest alone, and measure before and after.

  • databases
  • database index
  • performance
  • backend
  • software engineering

Keep reading

From across the family

More reading from the other Adyakshar Labs products.

Sampad AI
What to Do in the First Two Days After a Tax Notice ArrivesA tax notice rarely gets worse in two days. It gets worse when it sits. A short checklist for capturing the facts, updating the client and planning the reply.
Quantum Labs
Harvest Now, Decrypt Later: How to Decide What to Protect FirstEncrypted data can be recorded today and read later. You cannot protect everything at once, so sort it by shelf life. A plain test and a three-tier list for deciding what comes first.
Bindus
How Long Did That Job Really Take? Using Time Sheets and Milestones HonestlyMost small teams quote from memory and learn the real number after the job is done. A simple habit with time sheets and milestones makes the next quote better than the last.