Database Indexing Strategies: Speed Up Your SQL Queries

What Are Database Indexes?

An index is a data structure maintained by the database engine that provides a fast lookup path to rows in a table. For selective filters, an index can reduce the rows or pages read. The optimizer may still choose a table scan for small tables or queries returning many rows. Measure the plan and timing instead of assuming a fixed speedup.

How B-Tree Indexes Work

The most common index type across PostgreSQL, MySQL, SQL Server, and SQLite is the B-tree (balanced tree). Think of it like a phonebook: instead of reading every name from A to Z, you open to the approximate section, narrow down to the right page, and land on the entry. A B-tree works similarly:

• The tree is organized into nodes. Each internal node contains sorted keys and pointers to child nodes.
• A lookup starts at the root, compares the search value against node keys, and follows the correct pointer down through branch nodes.
• It reaches a leaf containing matching entries. Depending on the engine and index, entries identify rows, contain a clustered key, or hold the row data itself.
• The tree stays balanced, but its depth and I/O depend on key width, page size, row count, caching, and whether the query also fetches table pages.

-- Basic index on a single column
CREATE INDEX idx_employees_email ON employees (email);

-- The database now maintains a B-tree on the email column.
-- A selective filter is a candidate for an index lookup; check EXPLAIN:
SELECT * FROM employees WHERE email = '[email protected]';

When Should You Use Indexes?

Indexes are not free. They consume disk space and add overhead to every INSERT, UPDATE, and DELETE because the engine must keep the index in sync with the table data. Use them strategically:

• Columns in WHERE clauses. A suitable index can help a frequent selective filter; its benefit depends on the data distribution and chosen plan.
• Columns in JOIN conditions. Indexes may help foreign-key joins and parent updates or deletes; a large join can still favor a scan.
• Columns in ORDER BY and GROUP BY. An index can provide pre-sorted data, eliminating an expensive sort operation.
• High-cardinality columns. Columns with many distinct values (like email or UUID) benefit more than columns with few distinct values (like a boolean is_active flag).
• Large tables. A table with 100 rows rarely needs extra indexes. A table with 10 million rows almost certainly does.

As a general rule: start without extra indexes, measure query performance with EXPLAIN, then add indexes where the data tells you to.

Types of Indexes

Single-Column Index

The simplest form. Creates a B-tree on one column. Ideal for queries that filter or sort on that column alone.

-- PostgreSQL / MySQL / SQLite
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id);

Composite (Multi-Column) Index

An index on two or more columns. Equality filters on leading columns and a range on the next often form an efficient B-tree access path. Other conditions may still use a scan or skip scan; support and cost vary by engine and version.

CREATE INDEX idx_orders_status_date
    ON orders (status, ordered_at);

-- Leading equality plus a date range is a candidate for this index:
SELECT * FROM orders
WHERE status = 'shipped' AND ordered_at > '2025-01-01';

-- Filtering on the leading column may also use this index:
SELECT * FROM orders WHERE status = 'pending';

-- A date-only filter may scan or use a skip scan; inspect the plan:
SELECT * FROM orders WHERE ordered_at > '2025-01-01';

Choose column order from equality filters, range conditions, sorting, and coverage needs. “Most selective first” is not a universal rule. PostgreSQL 18 can use B-tree skip scans for suitable non-leading filters.

Unique Index

Enforces a uniqueness constraint while also providing index-based lookup performance. Most databases automatically create a unique index when you define a UNIQUE constraint or PRIMARY KEY.

CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Enforces uniqueness; constraint metadata and eligible FK targets vary by engine:
-- ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

Partial (Filtered) Index

Indexes only the rows that satisfy a WHERE condition. This keeps the index small and highly targeted.

-- PostgreSQL partial index
CREATE INDEX idx_orders_active
    ON orders (customer_id)
    WHERE status NOT IN ('delivered', 'cancelled');

-- SQL Server: a supported simple comparison filter
CREATE INDEX idx_orders_pending
    ON orders (customer_id)
    WHERE status = 'pending';

A partial or filtered index can help a small, frequently queried subset. The query predicate must imply the filter. SQL Server’s filter syntax is more restricted than PostgreSQL’s; do not copy arbitrary predicates between them.

Other Index Types

Common Indexing Mistakes

Even experienced developers fall into these traps. Avoid them to keep your database performing well.

1. Over-Indexing

Adding an index on every column “just in case” wastes disk space and slows down writes. Each index must be updated on every insert, update, and delete. A table with 15 indexes will have significantly slower write throughput than one with 3 well-chosen indexes.

2. Indexing Low-Cardinality Columns

Indexing a boolean is_active column that contains only TRUE and FALSE can be useful when values are highly skewed or the index covers a narrow query. Cardinality alone does not decide whether an index helps. If you must filter on such columns, combine them in a composite index with a higher-cardinality column, or use a partial index.

3. Ignoring Query Patterns

An index is only useful if your actual queries can leverage it. Always examine the slow queries in your application first, then design indexes that target those specific access patterns.

-- Use EXPLAIN to see if your index is being used
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 7 AND status = 'shipped';

-- Look for "Index Scan" or "Index Only Scan" in the output.
-- Bitmap Index Scan is another index access path. Seq Scan can be appropriate.

4. Forgetting to Index Foreign Keys

In PostgreSQL, foreign-key columns are not automatically indexed (unlike MySQL’s InnoDB, which creates an index on foreign keys by default). An index can help selective joins and referential checks, but the optimizer may choose a scan or hash join. Check for an existing index whose leading columns already cover the foreign key before adding another.

-- Consider these only when equivalent access paths do not exist
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

5. Never Reviewing Unused Indexes

Over time, application code changes and old indexes may no longer serve any query. Unused indexes still consume storage and slow down writes for zero read benefit.

Monitoring Index Usage

Modern databases provide system views that report how often each index is used. Regularly reviewing these helps you drop unused indexes and spot missing ones.

PostgreSQL

-- Find unused indexes (0 scans since last stats reset)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

MySQL

-- MySQL: no row fetches recorded since these counters were reset
SELECT
    object_schema,
    object_name AS table_name,
    index_name,
    count_fetch AS row_fetches
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_fetch = 0
  AND object_schema = 'your_database';

Treat zero reads as a review candidate, not permission to drop an index. Check when counters were reset, whether instrumentation is enabled, and whether seasonal queries, constraints, or foreign-key checks need it. Test a proposed removal with a representative workload.

Indexes in Schema Design and ERDs

While ERDs primarily focus on tables, columns, and relationships, indexes are an important part of the overall schema design. When planning your database, consider indexes alongside your table definitions:

• A primary key normally has an engine-managed access path. SQLite INTEGER PRIMARY KEY uses the rowid rather than a separate unique index; check the engine before adding a duplicate index.
• Review access paths for foreign keys. PostgreSQL does not create child-side indexes automatically, but an existing composite index may already cover the relationship.
• Columns with UNIQUE constraints receive an automatic unique index.
• Additional indexes for frequently queried columns should be documented alongside your schema DDL so they are not forgotten during deployment.

-- Include indexes in your schema DDL files for reproducibility
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category_id INT REFERENCES categories (id),
    price       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_created ON products (created_at DESC);

Visualize your full schema—tables, relationships, and the columns you plan to index—by pasting your DDL into the Erd Go editor. Seeing the structure visually often reveals columns that need indexing (like frequently joined foreign keys) that you might overlook when reading raw SQL.

References and further reading

• PostgreSQL: multicolumn indexes and skip scans
• Microsoft: SQL Server filtered indexes
• PostgreSQL: interpreting EXPLAIN plans