Every query starts with a search.

Without indexes, the database checks each row, one by one. As the table grows, queries slow down.

Indexes solve that.

They create a path for the database engine to reach the right data faster. Whether it's a B-tree, a hash, or a composite key, the goal stays the same: reduce how much data the engine has to scan.

But not all indexes help. While they speed up reads, they also add storage and slow down inserts, updates, and deletes. Creating an index is a technical decision that affects performance and cost.

‍

What Is Database Indexing?

Database indexing helps the engine find data faster by skipping full table scans.

It works by creating a separate structure. This structure links one or more column values to the place on disk where each row lives. Instead of searching through every row, the database checks the index to go straight to what it needs.

The structure can be a B-tree, hash table, or another format built for quick lookups.

Indexes do not store full rows. They store keys from selected columns, along with pointers to the actual data. Some indexes, like covering indexes, store extra columns too. This lets the query run without touching the main table.

There are two main types:

• A clustered index changes how rows are stored on disk to match the index. Each table can only have one.
• A non-clustered index is stored separately. A table can have many of these.

Indexes work best on columns used often in filters, joins, or sorting. They save time, but they also use more storage and make write operations slower.

That is why indexing is not just a small optimization. It is a key part of how databases scale.

‍

How Indexes Work Behind the Scenes

To understand indexing, it helps to know how data is stored.

Tables are made of rows. Each row lives on a disk in blocks or pages. Without indexes, the database checks these one by one. This is called a full table scan.

An index creates a lookup structure. Most indexes use a B-tree or B+ tree. These are balanced trees, so the time it takes to find any value is nearly the same, even on large tables.

Each leaf node in the tree holds a value from the indexed column and a pointer to the full row.

Here’s what happens when a query runs:

• The engine walks through the index to find the value.
• Once found, it uses the pointer to grab the row.
• If all the needed columns are in the index, it skips reading the main table.

In SQL Server, clustered indexes store the actual data in the leaf nodes. In non-clustered indexes, the leaf nodes only store pointers.

This difference affects performance.

‍

Why Indexing Helps and When It Doesn't

Indexes work best when the database knows what it is looking for.

If you filter by a primary key, the engine goes straight to the row. The same goes for joins or sorting on indexed fields.

But some filters stop indexes from working. For example, functions like LOWER() or filters like LIKE with a wildcard at the start make indexes useless unless you build special indexes.

Some databases let you create indexes on expressions like UPPER(last_name) or on specific rows using partial indexes. These give you more control.

But indexing also adds cost:

• It takes up disk space
• It slows down inserts and updates
• It makes write-heavy tables harder to manage

So you should only index what helps your queries.

‍

Common Index Types You Will Use

Not all indexes are the same. Some are built for speed with exact matches. Others are better for ranges or sorting. Here are the types you will use most often.

‍

Clustered Index

This index controls how data rows are stored on disk. The table itself is sorted to match the index key.

You can only have one clustered index per table.

Use it when:

• You need to read data in order, like by date or ID
• You query ranges often
• You want fast access to nearby rows

Changing a clustered index later takes time and resources, so plan ahead.

‍

Non-Clustered Index

This index keeps a separate structure. It does not change how rows are stored.

It holds the key and a pointer to the row. The table can have many non-clustered indexes.

Use them when:

• You filter on many different columns
• You want to speed up queries without changing row order
• Your queries follow different patterns

‍

Hash Index

Hash indexes use a hash function to map values to a location. They are fast for exact matches.

Example:

‍

SELECT * FROM users WHERE email = 'x@y.com';

They are not useful for ranges or sorting.

Use hash indexes for in-memory databases or key-value lookups.

‍

Covering Index

A covering index includes all the columns a query needs. This means the engine can answer the query without reading the full row from the table.

Example in SQL Server:

‍

CREATE INDEX ix_orders_customer_date 
ON orders (customer_id) 
INCLUDE (order_date, total);

This avoids extra reads and saves time.

‍

Composite Index

Composite indexes include more than one column. They help when your query filters on more than one field.

Example:

‍

CREATE INDEX ix_city_lastname ON users (city, last_name);

Order matters. The index helps only if your query includes the leftmost column.

Use them when:

• Your queries use the same fields together
• The filters are always in the same order

Keep the index narrow to reduce write cost.

‍

Less Common Index Types That Solve Specific Problems

Some indexes are built for special use cases. You may not need them often, but they help when normal indexes fail.

‍

Bitmap Index

This index is best for columns with a few possible values. It stores bitmaps instead of row pointers.

Use it when:

• The column has low cardinality, like gender or region
• You need to combine filters
• Your workload is mostly reads

Avoid it on columns that change often.

‍

Reverse Index

This index flips the key before storing it. For example, 123 becomes 321.

It spreads inserts more evenly across the index tree.

Use it when:

• Your key values increase over time
• You want to avoid hotspots in write-heavy tables

‍

Partial Index

This index includes only rows that match a condition.

Example:

‍

CREATE INDEX ix_active_users 
ON users (last_login) 
WHERE status = 'active';

‍This saves space and improves performance on filtered queries.

Use it when:

• You query the same subset often
• Most of the table doesn't need indexing

‍

Function-Based Index

This index stores values from a function like LOWER(email). Without it, the engine would skip the index if your query uses a function.

‍

Example:

CREATE INDEX ix_lower_email 
ON users (LOWER(email));

‍This helps if your filters use functions.

‍

Indexing Constraints and Keys

Indexes are also used to enforce rules in the database.

When you define constraints like PRIMARY KEY or UNIQUE, the database creates indexes to support them.

This makes inserts, updates, and lookups faster and more reliable.

‍

Primary Key Index

Most databases create a clustered index for the primary key by default.

It ensures each row has a unique identifier and gives fast access to that row.

‍

Unique Index

This index makes sure that no two rows have the same value in a column.

‍

Example:

CREATE UNIQUE INDEX ix_unique_email ON users (email);

It blocks duplicates and speeds up queries on that field.

‍

Foreign Key Indexing

When one table references another using a foreign key, add an index on the foreign key column.

‍

Example:

CREATE INDEX ix_orders_customer_id ON orders (customer_id);

This helps with joins and keeps constraint checks fast.

Some databases do this automatically. Others do not.

‍

Choosing the Right Index

Good indexing is about matching your data to how it is used.

Here’s how to choose the right index.

‍

Start With Queries

Look at how your application queries the data.

Ask:

• Which columns are used in WHERE?
• What fields are used in JOINs?
• What shows up in ORDER BY or GROUP BY?

Your index should support the most common patterns.

‍

Use High Selectivity

Index columns with many unique values. These give the best results.

Examples: ID, email, SKU

Avoid indexing:

• Boolean fields
• Fields with repeated values, unless part of a partial or composite index

‍

Balance Reads and Writes

Indexes help reads but slow down writes. Every insert or update must also update the index.

If your table gets a lot of writes, use fewer indexes. If it gets mostly reads, more indexes may help.

‍

Avoid Duplicate Indexes

You do not need indexes that overlap.

‍

Example:

CREATE INDEX ix_first_name ON users (first_name);
CREATE INDEX ix_first_last ON users (first_name, last_name);

‍‍The second index already covers first_name. Drop the first.

Use tools like EXPLAIN or query plans to see what your database is using.

‍

Keep Indexes Small

The more columns you index, the slower the writes.

If you only need extra fields in SELECT, use INCLUDE when possible.

‍

Example:

CREATE INDEX ix_orders_status 
ON orders (status) 
INCLUDE (created_at, total);

‍This keeps the index light and still helps the query.

‍

FAQ

‍

What is a database index?

A database index is a structure that helps the database find data faster. Instead of scanning every row, the engine looks up a value using a map of keys and pointers. This improves query speed, especially in large tables.

‍

When should I create an index?

Create indexes on columns that are:

• Used in WHERE filters
• Part of a JOIN
• Used in ORDER BY or GROUP BY
• Marked as PRIMARY KEY or UNIQUE

Avoid indexing columns with very few unique values unless combined with other filters.

‍

What is the difference between clustered and non-clustered indexes?

A clustered index sorts the data rows to match the index. The table is stored in that order, and there can only be one.

A non-clustered index is separate from the data. It stores keys and pointers. You can have many non-clustered indexes on a single table.

‍

Can I have more than one index on a table?

Yes. A table can have many non-clustered indexes. This helps support different query types. Just keep in mind that each index slows down writes and takes space.

‍

What is a composite index?

A composite index includes two or more columns. It helps when queries use more than one field to filter.

‍

Example:

SELECT * FROM users WHERE city = 'Boston' AND last_name = 'Nguyen';

The column order matters. The query must include the first column for the index to help.

‍

Do indexes make inserts and updates slower?

Yes. When data changes, the database must also update the index. The more indexes you have, the more work the database does on every write.

‍

What is a covering index?

A covering index holds all the fields needed for a query. This means the database does not have to read the full row. It improves performance by using fewer reads.

‍

What is a hash index?

A hash index uses a hash function to place keys into buckets. It works well for exact-match lookups but does not support range queries or sorting.

‍

What is a partial index?

A partial index covers only the rows that meet a condition.

‍

Example:

CREATE INDEX ix_lower_email ON users (LOWER(email));

This type of index saves space and makes write operations faster.

‍

How do I know if my index is being used?

Use the query planner for your database. In PostgreSQL, use EXPLAIN ANALYZE. In SQL Server, check the execution plan. These tools show if your query is using an index and how well it performs.

‍

Can I index a calculated column?

Yes, if your database supports it. These are called function-based or expression indexes.

‍

Example:

CREATE INDEX ix_lower_email ON users (LOWER(email));

This is helpful when queries use LOWER, UPPER, or other functions in WHERE clauses.

‍

Do indexes require more storage?

Yes. Every index takes up space. Indexes with more columns or with included fields take more space than simple ones.

‍

Can indexes improve JOIN performance?

Yes. Indexes help the database find rows quickly when joining tables.

‍

Example:

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

Adding an index on orders.customer_id makes this faster.

‍

Why is my index not being used?

Common reasons include:

• The query uses a function but the index was created on the raw column
• The filter condition returns too many rows
• A wildcard is used at the start of a string, like %term
• The query uses OR conditions that make the index less useful
• The planner decides a full table scan is faster

Use the query planner to find out why.

‍

Is indexing always worth it?

No. Indexes help with reads but can slow down writes and use more storage. Create indexes that match real query needs. Remove ones that are not used.

‍

Summary

Database indexing speeds up data retrieval by pointing the database to the right rows without scanning the whole table.

Use clustered indexes when data needs to be sorted. Use non-clustered indexes for flexible queries. Add covering indexes to skip table lookups. Use hash indexes for fast exact matches. Composite, partial, and function-based indexes solve more specific needs.

But too many indexes can slow down writes and use more storage. Only create the ones your queries actually need.

Smart indexing keeps systems fast and efficient.