What is an index in SQL, the types (primary, unique, non-unique, composite, clustered vs non-clustered), and how to create one with `CREATE INDEX`

• Index – An auxiliary on-disk data structure, usually a balanced B-tree, that stores a sorted copy of one or more column values together with pointers to the full rows.
• Purpose – It lets the database locate matching rows in O(log n) time instead of scanning every row, at the cost of extra storage and slower writes.
• Primary-key index – A unique, NOT NULL index that identifies each row; most engines make it the clustered index automatically.
• Unique index – Enforces that no two rows share the same value in the indexed columns, protecting data integrity at the database layer.
• Composite index – Spans multiple columns in a defined order and obeys the left-prefix rule for query matching.
• Clustered vs non-clustered – A clustered index physically orders the rows by the key (one per table), while a non-clustered index is a separate structure that points back to the rows (many per table).

What is an index?

An index is a separate, sorted data structure that the database maintains alongside the table data. It stores a copy of the indexed column values in sorted order, together with a pointer (a row locator) back to the full row. When a query filters, joins, sorts, or groups by an indexed column, the database navigates the index instead of scanning every row.

In our National Bank of India system, the accounts table has millions of rows. Without an index on account_id, every lookup scans the entire table.

-- National Bank of India: accounts table
CREATE TABLE accounts (
    account_id   VARCHAR(20) PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    pan          VARCHAR(10)  NOT NULL,
    branch_code  VARCHAR(10)  NOT NULL,
    balance      DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status       VARCHAR(10)  NOT NULL DEFAULT 'ACTIVE',
    created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The PRIMARY KEY declaration automatically creates a B-tree index on account_id, so lookups by account number are logarithmic.

Why do indexes exist?

Indexes exist because sequential scanning becomes unacceptable as tables grow. A full scan of ten million rows reads every page on disk. An index narrows the read to a handful of pages.

-- Without an index on customer_name, this scans the entire table
SELECT account_id, balance
FROM   accounts
WHERE  customer_name = 'Rajesh Kumar';

-- EXPLAIN shows Seq Scan: cost = high, rows examined = 10,000,000

After creating an index, the same query becomes an index seek.

CREATE INDEX ix_accounts_name ON accounts(customer_name);

-- Now EXPLAIN shows Index Scan on ix_accounts_name: cost = low
SELECT account_id, balance
FROM   accounts
WHERE  customer_name = 'Rajesh Kumar';

What are the types of indexes?

There are five common types, and each serves a different purpose.

Primary-key index

Created automatically when you declare PRIMARY KEY. It enforces uniqueness and not-null. In InnoDB and SQL Server it is also the clustered index.

-- The PRIMARY KEY gives you a unique clustered B-tree on account_id for free
CREATE TABLE accounts (
    account_id VARCHAR(20) PRIMARY KEY,
    balance    DECIMAL(15,2) NOT NULL
);

Unique index

Enforces uniqueness on a non-primary-key column. At National Bank of India every customer has a PAN number that must be unique.

CREATE UNIQUE INDEX ux_accounts_pan ON accounts(pan);

-- Any duplicate PAN insert fails with a constraint violation
INSERT INTO accounts (account_id, customer_name, pan, branch_code, balance)
VALUES ('SAV3001', 'Priya Sharma', 'ABCDE1234F', 'MUM001', 50000.00);

-- This fails if PAN 'ABCDE1234F' already exists
INSERT INTO accounts (account_id, customer_name, pan, branch_code, balance)
VALUES ('SAV3002', 'Amit Verma', 'ABCDE1234F', 'DEL001', 30000.00);
-- ERROR: duplicate key value violates unique constraint "ux_accounts_pan"

Non-unique index

Allows duplicate values and exists purely for lookup speed. Many accounts share the same branch code.

CREATE INDEX ix_accounts_branch ON accounts(branch_code);

-- Speeds up branch-level queries without forbidding duplicates
SELECT account_id, customer_name, balance
FROM   accounts
WHERE  branch_code = 'MUM001';

Composite index

Covers more than one column in a defined order.

CREATE INDEX ix_txn_acct_date
    ON transactions(account_id, created_at);

-- Serves queries that filter by account and sort by date
SELECT txn_id, amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
ORDER BY created_at DESC
LIMIT 10;

Clustered vs non-clustered

A clustered index determines the physical row storage order. There can be only one per table. A non-clustered index is a separate B-tree with pointers back to the row.

| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Row storage | Rows live in the leaf nodes | Leaf holds key + row pointer |
| Count per table | Exactly one | Many |
| Extra lookup | None – leaf is the row | Bookmark lookup to fetch row |
| Range scan speed | Very fast – data is contiguous | Slower – random heap fetches |
| Example engine | InnoDB PK, SQL Server PK | PostgreSQL PK (heap-based) |

How is a clustered index different from a non-clustered index?

In InnoDB, the primary key is the clustered index. The leaf pages of the primary-key B-tree contain the actual row data. Reaching the key gives you the full row with no extra hop.

A non-clustered (secondary) index leaf holds only the indexed columns plus the primary key as a pointer. To get the remaining columns, the engine does a second traversal into the clustered index. This is the bookmark lookup.

-- Clustered index lookup: one traversal, full row returned
SELECT * FROM accounts WHERE account_id = 'SAV2001';
-- Plan: Index Scan using accounts_pkey (clustered)

-- Non-clustered index lookup: two traversals
SELECT * FROM accounts WHERE pan = 'ABCDE1234F';
-- Plan: Index Scan on ux_accounts_pan -> then fetch from clustered index

PostgreSQL differs: its primary key is a unique non-clustered index over a heap table. The CLUSTER command reorders the heap once, but the order drifts as rows change.

How do you create and drop indexes?

The basic syntax works across all major engines.

-- Create a non-unique index
CREATE INDEX ix_accounts_branch ON accounts(branch_code);

-- Create a unique index
CREATE UNIQUE INDEX ux_accounts_email ON accounts(email);

-- Create a composite index
CREATE INDEX ix_txn_acct_date ON transactions(account_id, created_at);

-- Drop an index
DROP INDEX ix_accounts_branch;

-- PostgreSQL: build without blocking writes on a busy table
CREATE INDEX CONCURRENTLY ix_txn_amount
    ON transactions(amount);

What are the trade-offs of indexing?

Every index has a cost. The database must update every relevant index on every INSERT, UPDATE, and DELETE. More indexes mean slower writes.

-- National Bank of India: transactions table with 3 indexes
CREATE TABLE transactions (
    txn_id      BIGSERIAL PRIMARY KEY,
    account_id  VARCHAR(20) NOT NULL,
    amount      DECIMAL(15,2) NOT NULL,
    txn_type    VARCHAR(10) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ix_txn_acct_date ON transactions(account_id, created_at);
CREATE INDEX ix_txn_amount    ON transactions(amount);
CREATE INDEX ix_txn_type      ON transactions(txn_type);

-- Every INSERT now updates 4 B-trees (PK + 3 secondary indexes)
INSERT INTO transactions (account_id, amount, txn_type)
VALUES ('SAV2001', 15000.00, 'CREDIT');

| Benefit | Cost |
|---|---|
| Faster WHERE lookups | Slower INSERT, UPDATE, DELETE |
| Faster JOIN matching | Extra disk space per index |
| Faster ORDER BY, GROUP BY | Write amplification |
| MIN/MAX from index tips | Memory pressure from more pages |

What are common mistakes with indexes?

The most common mistake is creating indexes speculatively without checking whether the query actually uses them.

-- Wrong: index on a low-selectivity column
CREATE INDEX ix_accounts_status ON accounts(status);
-- 95% of accounts are 'ACTIVE', so the optimizer ignores this index

SELECT * FROM accounts WHERE status = 'ACTIVE';
-- EXPLAIN: Seq Scan (not Index Scan) because almost all rows match

-- Better: partial index on the rare value
CREATE INDEX ix_accounts_frozen
    ON accounts(account_id)
    WHERE status = 'FROZEN';

Another mistake is wrapping the indexed column in a function, which defeats the index.

-- Wrong: function on the indexed column
SELECT * FROM transactions
WHERE YEAR(created_at) = 2026;
-- EXPLAIN: Seq Scan – the B-tree is sorted by full timestamps, not extracted years

-- Correct: sargable range on the raw column
SELECT * FROM transactions
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';
-- EXPLAIN: Index Scan on ix_txn_acct_date

How do you verify an index is being used?

Never assume an index is used. Always check the execution plan.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT txn_id, amount, created_at
FROM   transactions
WHERE  account_id = 'SAV2001'
  AND  created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 10;

Expected output:
Limit (actual time=0.035..0.042 rows=10)
  -> Index Scan Backward using ix_txn_acct_date
       on transactions
       Index Cond: (account_id = 'SAV2001'
         AND created_at >= '2026-01-01')

If you see Seq Scan instead of Index Scan, investigate: a function on the column, stale statistics, low selectivity, or a type mismatch.

-- Refresh statistics after a large data load
ANALYZE transactions;

What should a senior engineer know?

A senior engineer should know these production-level details.

• In InnoDB every secondary index stores the primary key as its row pointer, so a wide primary key (like a 36-character UUID string) bloats every secondary index.
• PostgreSQL's primary key is a unique non-clustered index over a heap, unlike InnoDB where it is the clustered index.
• Index-only (covering) scans avoid heap access entirely when all needed columns are in the index.
• CREATE INDEX CONCURRENTLY in PostgreSQL builds the index without blocking writes on a busy table.
• After large data loads, run ANALYZE to update statistics so the optimizer makes correct decisions.

// com.bank.service.AccountLookupService
package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class AccountLookupService {

    // Uses the primary-key clustered index: one B-tree traversal
    public BigDecimal getBalance(Connection conn, String accountId)
            throws SQLException {
        String sql = "SELECT balance FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId); // match VARCHAR type
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getBigDecimal("balance");
                }
                throw new IllegalArgumentException(
                    "Account not found: " + accountId);
            }
        }
    }
}

What are common interview follow-up questions?

• What is the left-prefix rule for composite indexes?
• When does the database ignore an index?
• What is a covering index and when does it produce an index-only scan?
• Why do random UUID primary keys cause index fragmentation?
• How do you build an index on a busy production table without blocking writes?