What Is Database Indexing? Examples for MySQL and Postgres

Database indexing is the practice of creating an additional data structure that helps the database find rows faster. Instead of scanning every row in a table, the database can use the index to narrow the search and retrieve matching records more efficiently.

That definition is the short version. The practical version is this: indexing trades some extra storage and write overhead for much faster reads. If you query large tables and filter or sort on the same columns repeatedly, the right index can turn a slow query into a fast one.

The Quick Answer

A database index is a lookup structure built from one or more columns so the database can find data without scanning the entire table.

A book index is a useful analogy. If you want to find every page where “transactions” appears, you do not read the whole book front to back. You jump to the index, find the term, and go straight to the relevant pages. A database index works the same way for queries.

How Database Indexing Works

When you create an index, the database stores ordered references based on the indexed columns. In many systems, the default structure is a B-tree. That structure makes it efficient to look up values, scan ranges, and maintain order.

Suppose you have a table of one million orders and you frequently search by customer_id. Without an index, the database may need to inspect every row. With an index on customer_id, it can jump into the relevant branch of the structure and find matching rows much faster.

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 101;

If this query runs all day in an application, indexing customer_id is often one of the highest-value optimizations you can make.

Why Indexes Matter

Indexes matter because most real applications are read-heavy. Dashboards, search results, admin lists, account pages, reports, and APIs all depend on fast retrieval.

When indexes are missing, performance often degrades gradually. The app works with ten thousand rows, then becomes sluggish at one million. That is why indexing is less about theory and more about scalability. It lets the same query pattern survive as data volume grows.

Indexes also affect sorting and joins. Queries that order by a column, join related tables, or filter on multiple predicates can benefit dramatically from the right index design.

Types of Database Indexes

Single-column index: built on one column such as email or created_at.

Composite index: built on multiple columns such as (account_id, created_at). These are useful when queries filter on a predictable combination of columns.

Unique index: enforces uniqueness and speeds lookups. Common examples include usernames and email addresses.

Clustered index: defines the physical order of rows in some database systems. The exact implementation differs by vendor.

Non-clustered index: stores separate references to rows rather than controlling physical storage order.

Specialized indexes: some engines offer full-text, spatial, hash, GIN, GiST, or bitmap-style indexes for specific workloads.

Creating Your First Index

A practical indexing guide should show the actual commands. Here is a simple example using a customer lookup pattern.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

Now compare the execution plan before and after adding the index.

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 101;

Before the index, you may see a sequential scan or full table scan. After the index, you ideally see an index scan, bitmap index scan, or another plan that touches far fewer rows.

A second common example is sorting recent orders per account.

CREATE INDEX idx_orders_account_created_at
ON orders (account_id, created_at DESC);

This kind of composite index can help both filtering and ordering when the query shape matches the index order.

When to Add an Index

Add an index when a query is important, repeated, and slow enough to matter. Good candidates include:

• frequently filtered columns such as foreign keys, status fields, and timestamps
• join columns used to connect related tables
• columns used in unique constraints
• sorting columns in performance-sensitive lists and reports

Do not add indexes blindly. Every index increases storage usage and makes inserts, updates, and deletes more expensive because the index must also be maintained.

When to Remove or Avoid an Index

Indexes are not free. Redundant or unused indexes can slow writes and consume memory without helping any important query.

Avoid creating multiple overlapping indexes without evidence. For example, if you already have an index on (account_id, created_at), a separate index on account_id may or may not still be necessary depending on your workload and database engine.

Also be careful with low-cardinality columns such as booleans. An index on a column with only two values may not be selective enough to help unless combined with other predicates.

Indexing Best Practices

• Start with the real slow queries, not abstract rules.
• Use EXPLAIN or execution plans before and after changes.
• Index foreign keys and common join paths.
• Prefer one well-designed composite index over several overlapping indexes.
• Review write cost, especially on high-ingest tables.
• Periodically remove unused indexes.
• Match the index design to the exact query shape you care about.

Indexing in PostgreSQL, MySQL, and SQL Server

PostgreSQL uses B-tree indexes by default and also supports powerful specialized types such as GIN and GiST. It is common to inspect query plans with EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 101;

MySQL also uses B-tree structures for common indexes in InnoDB. Composite indexes are especially important because the order of columns affects whether the optimizer can use them efficiently.

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

SQL Server distinguishes clustered and non-clustered indexes explicitly. Included columns can also help cover queries.

CREATE NONCLUSTERED INDEX idx_orders_customer_id
ON dbo.orders (customer_id)
INCLUDE (order_date, total_amount);

Common Indexing Mistakes

One mistake is indexing everything. That usually creates write overhead without a matching read benefit.

Another mistake is ignoring query order. A composite index on (created_at, account_id) does not behave the same way as one on (account_id, created_at). Column order matters.

A third mistake is assuming an index always helps. If the query returns a huge percentage of the table, a sequential scan can still be cheaper.

How Indexing Relates to Records and Tables

Indexes do not replace tables. They sit alongside tables as access structures. The table still holds the records. The index helps the database decide how to locate those records quickly.

If you want to connect this concept back to the data model, read What Is a Record in a Database? (With Examples).

Key Takeaways

Database indexing is one of the most important performance concepts in relational systems. It speeds lookups, supports joins and ordering, and helps query performance scale with table size.

The practical workflow is simple: identify an important query, inspect the execution plan, add the right index, and verify the result. That is how indexing stops being theory and becomes a useful engineering tool.