Understanding Database Indexing: The Secret to Very Fast Queries

Your queries have gone sluggish even though the data only just crossed a million rows? Before you rush to upgrade the server, check the indexes. Here is how database indexing actually works.

Read in Indonesian

Your queries suddenly went slow even though the data only just crossed a million rows? Relax, you are not alone. 😅

Usually our first reaction is to rush into upgrading the database server spec (vertical scaling). In many cases, the root cause is trivial: the index is missing, or it was installed wrong.

Index is one of the most fundamental features, and one of the most effective, for speeding up data lookup in a relational database like PostgreSQL or MySQL. Let's take apart how it works.

Key takeaways

  • An index turns a wasteful O(N) Full Table Scan into a much nimbler O(log N) B-Tree Index Scan.
  • WHERE, JOIN, ORDER BY, and GROUP BY clauses are where an index most often speeds a query up.
  • The price you pay is extra disk space and the cost of updating the B-Tree structure on every write.
  • Small tables or low-cardinality columns, like a jenis_kelamin column, do not need an index at all.

Why Does a Search Without an Index Hurt?

Imagine looking up the definition of the word "Concurrency" in a 1,000-page encyclopedia.

If you have to read from page 1, word by word, until you find it on page 642, that is exhausting. 🤔 It also takes forever. This brutal approach is called a Full Table Scan in database terms.

It is a different story when you open the index section at the very back. There the word "Concurrency" is sorted alphabetically, complete with page numbers. You jump straight to page 642 without combing the other pages. That second style is what we call an Index Scan.

Simple, but the difference is like night and day.

What Actually Happens Behind the Scenes?

By default, almost every relational database uses a B-Tree (Balanced Tree) data structure to store its indexes.

The B-Tree structure lets the database find data in O(log N) time complexity. That means to find 1 row among 1,000,000 rows, the database needs about 20 comparisons. 😎 Far cheaper than checking a million rows one by one.

So picture an index as a set of sticky tabs in a notebook. You do not read every page each time you want to find one chapter.

Is There Any Downside to an Index?

There is, of course. There is no free lunch. 😄

An index needs extra disk space to store its pre-sorted data. On top of that, every time a row is inserted or updated, the database has to tidy the B-Tree structure along with it.

The effect: write operations, meaning INSERT, UPDATE, and DELETE, can become slightly slower. There is more work to do. So an index is not a case of more being better.

When Is It Required, When Is It Better to Skip?

The easy rule: install an index on columns that your search queries keep hitting.

  • Columns that often appear in a WHERE clause, for example WHERE email = 'user@example.com'.
  • Columns often used to connect tables, in other words JOIN.
  • Columns used for sorting (ORDER BY) or grouping (GROUP BY).

There are also conditions where an index buys you nothing:

  • The table is small, only tens or hundreds of rows, so a Full Table Scan is actually faster.
  • The column value changes often (frequent write).
  • The column has little variation (low cardinality), for example jenis_kelamin holding only L or P.

A jenis_kelamin column is like finding a book by its cover color alone. Not much help, hehe.

Example: Building an Index in SQL

Say you have a users table holding millions of rows.

SQL
-- Search query without an index (slow once the data hits millions)
SELECT * FROM users WHERE email = 'daffa@example.com';

-- Create a single-column index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Create a composite index for a two-column WHERE
CREATE INDEX idx_users_status_created ON users(status, created_at);

Pay attention: the column order in a composite index like (status, created_at) matters. If you only search by created_at without status, the index often goes unused, because the order does not match your query pattern.

So, How Do You Install Indexes Wisely?

In short, an index is a secret shortcut inside a labyrinth of data. With the right B-Tree configuration, a search across millions of rows finishes in an instant, without draining the server CPU.

The key is balance: install indexes on columns that WHERE, JOIN, or ORDER BY keep hitting, but do not overdo it, so write stays fast and disk stays roomy.

For anyone who wants to go deeper on indexing, use-the-index-luke.com is the reference to read.

Happy indexing, and may your queries always run smoothly down the B-Tree corridors. 👋

Get better at shipping code.

Join the other developers getting tech updates, tips, and relaxed tutorials in their inbox every week.

Stay in the loop

Tech updates, coding tips, and relaxed insights straight to your inbox every week.

No spam. Unsubscribe anytime.

Read next

See all

For developers who learn by struggling

© 2026 Inva.dev.