Picture an online store with a million customers. Someone types their email into the login form and waits. Behind the scenes, the database starts at the first row of the customers table and checks every single record until it finds a match. On a small test database, this felt instant. In production, it takes several seconds, and users start leaving.

The fix is often a single line of code that creates an index. Indexes are one of the most powerful tools a developer has, and also one of the most misunderstood. Let's look at how they actually work, how to decide where they belong, and what they quietly cost you.

Think about the index at the back of a textbook. If you want every mention of “recursion,” you don't read all 600 pages. You flip to the index, find the word in alphabetical order, and jump straight to pages 112 and 340. A database index works the same way. It is a separate, sorted structure that points to where the real data lives.

Most databases store indexes as a B-tree, a balanced tree where each level narrows the search. Starting at the top, the database compares your value, chooses a branch, and repeats until it reaches the right entry. Because each step rules out most of the remaining options, finding one row among millions usually takes only three or four levels of lookups.

Without an index, the database performs a full table scan, reading every row. That's fine for a table of fifty settings, but painful for a table of fifty million orders. Sorted order helps beyond exact matches, too. Range queries like “orders from last week” and sorted results like “newest first” can walk the index in order instead of sorting everything from scratch.

Takeaway

An index trades a little extra organisation up front for dramatically less searching later. It's the difference between reading the whole book and checking the index at the back.

A common beginner instinct is to index every column just in case. Resist it. The right indexes come from studying how your application actually asks for data. Look at the WHERE clauses, JOIN conditions, and ORDER BY statements that run most often, especially on large tables. Those columns are your strongest candidates.

Selectivity matters too. An index on an email column is extremely useful because each value points to roughly one row. An index on a true-or-false column like is_active often isn't, because a huge share of the table matches either way, and scanning may be just as fast. Good index columns narrow the search dramatically.

When queries filter on several columns together, consider a composite index. An index on (customer_id, created_at) can quickly answer “show this customer's orders, newest first.” Column order matters. Like a phone book sorted by last name and then first name, it helps when you know the last name, but not when you only know the first. Most databases offer an EXPLAIN command that shows whether a query uses an index, so measure rather than guess.

Takeaway

Design indexes around the questions your application asks, not the shape of your tables. Start with real queries, then measure.

Here's the catch: every index must be kept up to date. When you insert a new row, the database writes it to the table and then updates every index on that table. Change an indexed column, and its index entry has to move. A table with eight indexes does nine writes for every insert.

For read-heavy systems like a blog or product catalogue, that's usually a great deal. Pages get viewed thousands of times for every edit. But in write-heavy systems, such as logging services, sensor data, or high-volume payment events, too many indexes can become the bottleneck themselves. Indexes also consume disk space and memory, and some grow larger than the data they describe.

The healthiest approach treats indexes as living decisions. Add them when real query patterns justify it, and review them as your application evolves. Features change, and an index that mattered two years ago may now be dead weight. Many databases can report which indexes are rarely used. Removing one is an easy win, because it speeds up every write while costing nothing on reads.

Takeaway

Every index is a promise to do extra work on every write in exchange for faster reads. Make that promise only when your queries will actually collect on it.

Indexes are a perfect example of a core design principle: organise data around how it will be used. A sorted structure turns an exhausting search into a few quick steps, but only when it matches the questions your application really asks.

Next time a query feels slow, run EXPLAIN before rewriting any code. Look for full table scans on large tables, add one targeted index, and measure again. Then hunt for indexes nobody uses. Small, deliberate changes beat guesswork every time.