A slow SELECT that used to complete in 20 milliseconds suddenly starts hanging for 12 seconds. The database grew from 50 thousand rows to 5 million, and every query turned into a lottery. Sound familiar?

Indexes are something everyone has heard of but few configure deliberately. You added a couple, things seemed faster, and you moved on. Then six months later INSERT is slower than SELECT without an index, because every insert rebuilds five unnecessary B-trees.

Let's break down how indexes actually work, what types exist, and most importantly, what rules to follow when building them so you don't cause havoc in production.

💡 Quick overview:

• Understand the mechanics of B-tree, hash, and composite indexes, because choosing the right type is impossible without this knowledge
• Master 7 key practices: from indexing foreign keys to dropping unused indexes
• Learn to read EXPLAIN and tell a useful index from a useless one

What is a database index

A database index is a separate structure that stores sorted values of one or more table columns along with pointers to the rows. When you run SELECT ... WHERE category_id = 42, the server without an index scans the table row by row (full table scan). With an index it finds the needed records in O(log n), like looking up a name in a phone book.

An index is created with CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

But the price of faster reads is slower writes. Every INSERT, UPDATE, and DELETE must update not only the table but all associated indexes. Three indexes on a table with a million rows, and bulk inserts slow down by an order of magnitude. The balance between read speed and write speed is the central question of index design.

A detailed breakdown of the topic by Hussein Nasser: 497 thousand subscribers, an engineering approach with no fluff. Using PostgreSQL as an example, he shows the internal mechanics of indexes, why one CREATE INDEX speeds up a query by 100x while another does nothing.

Index types: when to use which

The choice of index type determines how efficiently the database processes your query. Different DBMSs implement them differently, but the principles are universal.

B-tree (balanced tree)

The default type in most relational DBMSs. PostgreSQL, MySQL, Oracle, and SQL Server all use B-tree as the default index. It stores keys in sorted order, supports comparison operations, ranges (BETWEEN), prefix search (LIKE 'prefix%'), and sorting. It is the best choice for the vast majority of cases.

Hash index

Works only for exact equality comparisons =. Lightning fast on point queries but useless for ranges and sorting. In PostgreSQL, hash indexes became production-ready starting with version 10; in MySQL they are not available in InnoDB (only in MEMORY).

Clustered index

Defines the physical order of rows on disk. In MySQL InnoDB, the primary key is always clustered: rows are stored in PRIMARY KEY order. In SQL Server, there is one clustered index per table. Choosing the right clustered key (monotonically increasing: BIGSERIAL, AUTO_INCREMENT, or UNIQUEIDENTIFIER with NEWSEQUENTIALID()) provides gains on range queries and prevents page fragmentation.

Composite index

An index on two or more columns. Critical for queries that filter and sort by multiple columns:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Column order matters: put the column with the highest selectivity used in WHERE first. The leftmost prefix principle: an index (A, B, C) works for WHERE A and WHERE A AND B, but not for WHERE B AND C.

Covering index

Contains all columns the query needs, both for filtering and for output. The database retrieves everything from the index without touching the table. In PostgreSQL this is done with INCLUDE columns; in MySQL InnoDB, implicit covering happens through the clustered index.

7 key indexing practices

1. Index columns used in WHERE

The first rule: every column regularly used in WHERE, JOIN ... ON, and HAVING should be indexed. These are the operations that benefit from indexes the most.

Before creating an index, check the column's selectivity. If the status column has only three values ('new', 'processing', 'done') and there are 2 million rows, an index on status is nearly useless: the planner will choose a full table scan as the cheaper option. An index makes sense when the number of unique values is large enough relative to the table size.

2. Index columns used for sorting

ORDER BY without an index means filesort (MySQL) or explicit sort (PostgreSQL): the server collects all rows and sorts them in memory (or on disk if work_mem/sort_buffer_size is too small). An index on the same columns as ORDER BY comes for free, since the data is already sorted in the B-tree.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Index columns used in GROUP BY and aggregations

Grouping without an index requires a full scan and building a hash table. An index on the GROUP BY columns turns the operation into a streaming aggregation: the rows are already grouped in key order.

4. Index all foreign keys

An unindexed foreign key is a ticking time bomb. DELETE FROM users WHERE id = 5 with a FOREIGN KEY (user_id) REFERENCES users(id) on the orders table but no index on user_id means a full table scan of orders for every deletion. All popular DBMSs require an index on the foreign key or create one implicitly (MySQL InnoDB does it automatically, PostgreSQL does not).

5. Index unique columns and primary keys

The primary key is indexed automatically (often as a clustered index). An explicit UNIQUE INDEX protects against duplicates and also speeds up lookups. Any column with a business uniqueness constraint (for example email, slug, or external_id) should have a unique index, both for integrity and for performance.

6. Use the clustered index deliberately

For large tables (tens of millions of rows), the right clustered key is critical. A good choice is a monotonically increasing value: AUTO_INCREMENT, BIGSERIAL, or UUID v7. A random UUID as a clustered key causes page fragmentation: every insert lands at a random spot in the B-tree, splitting full pages into two half-filled ones.

7. Drop unused indexes

An index that no queries use is a pure loss. It slows down writes, takes up disk space and buffer pool memory, and misleads the query planner. In PostgreSQL, the system table pg_stat_user_indexes provides a list of unused indexes:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

In MySQL, similar information is available from sys.schema_unused_indexes (starting with version 5.7). Schedule a monthly audit and drop indexes that have not been used even once during the reporting period.

How to verify your indexes are working

Once you've created an index, verify that it is actually being used. The EXPLAIN command (or EXPLAIN ANALYZE) shows the query execution plan and actual index usage:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

In the output, look for Index Scan or Index Only Scan (PostgreSQL) / Using index (MySQL). If you see Seq Scan (PostgreSQL) or Using where; Using filesort (MySQL), the index is not being used. Possible reasons: low selectivity, wrong index type, mismatched column order in a composite index, or stale statistics (ANALYZE table_name;).

Monitor metrics regularly: pg_stat_user_indexes.idx_scan in PostgreSQL, sys.schema_index_statistics in MySQL. An index with zero scans over a month is a candidate for removal.

⁉️🤔 Frequently asked questions

How many indexes should a table have?

Ideally, 2 to 6 indexes per actively used table. Fewer than two almost certainly means some queries are suboptimal. More than six, and you should carefully check whether all of them are actually needed: every extra index slows down writes. For lookup tables (rare writes, frequent reads) more indexes are justified. For high-traffic operational tables (heavy INSERT/UPDATE) keep the count to a minimum.

How is a composite index better than several single-column indexes?

A composite index (A, B) is ONE structure. The server traverses it once. Three separate indexes (A), (B), and (C) for WHERE A=1 AND B=2 force the server to either pick one index (and filter the rest), or perform a bitmap index scan (merging bitmaps). A composite index is almost always more efficient, provided the column order matches your queries.

When does an index hurt rather than help?

Three typical scenarios. First: the table is small (up to a few thousand rows), and a full table scan is faster than reading the index plus fetching the rows. Second: an index on a column with low selectivity (is_deleted, status with three values). Third: bulk inserts during ETL/import, where indexes are rebuilt on every batch. Drop them before loading and recreate them afterward.

Should you index columns used in JOIN?

Absolutely. Every JOIN without an index on the joining column of the outer table is a nested loop with a full scan. For LEFT JOIN orders ON users.id = orders.user_id, an index on orders.user_id turns the nested loop into an index lookup. Always index the columns you join on.

B-tree or hash: which to choose for exact lookups?

For =, hash is faster: a hash lookup takes constant time, while B-tree traverses the tree in a logarithmic number of steps. But hash does not support ranges, sorting, or UNIQUE. In practice, B-tree covers the vast majority of scenarios; hash is a niche tool for point lookups by key in high-load systems (sessions, caches). In PostgreSQL, hash indexes have been production-ready since version 10 and take up less space than B-tree.

Should you index "just in case"?

No. Every index is a trade-off. It speeds up reads at the cost of slower writes and additional disk space. Don't index "just in case"; index for specific queries that actually run in your application. Profile slow queries (pg_stat_statements, slow_query_log), add indexes surgically, and check EXPLAIN before and after.

The main takeaway is simple: indexes are a tool, not a goal in themselves. One well-designed composite index can replace three single-column indexes and save gigabytes of disk space. And one unused index on a write-heavy table can slow down the entire application.

If you want to dig deeper, start with the official SQL Server index design guide and the PostgreSQL documentation on index types. And if you encounter a query that indexes can't fix, the problem may lie in the data model itself.