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_kelamincolumn, 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
WHEREclause, for exampleWHERE 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_kelaminholding onlyLorP.
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.
-- 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. 👋