Database Management System (DBMS) / Importance of Indexing

Secondary Index

🔍 What is Secondary Index?

A Secondary Index is an additional index created on non-primary key columns to speed up data retrieval based on non-unique or non-key attributes.

📌 Why Use a Secondary Index?

In many cases, you query data not based on the primary key.

For example:

SELECT * FROM Employees WHERE Department = 'Sales';

Here, Department is not a primary key — so searching would require a full table scan unless a secondary index is available.

🧠 Key Features:

Feature

Description

📦 Based on

Non-primary or non-clustering columns

🔑 Key Type

Can be non-unique

📚 Purpose

Speeds up queries using non-key attributes

🏗️ Structure

Typically a dense index (every record is indexed)

🔄 Does NOT define

The physical order of the table (unlike clustered index)

🔁 Multiple Allowed

You can create many secondary indexes per table

🔧 Example:

Assume this table:

EmpID (PK)

Name

Dept

101

Alice

HR

102

Bob

Sales

103

Charlie

HR

If you run:

SELECT * FROM Employees WHERE Dept = 'HR';

A secondary index on Dept might look like:

HR     → [101, 103]
Sales  → [102]

This allows the DBMS to directly fetch EmpIDs with 'HR' without scanning all rows.

🧱 Dense Secondary Index Example:

[Dept: HR] → Row 1
[Dept: Sales] → Row 2
[Dept: HR] → Row 3

All matching records are individually indexed.

📊 Primary vs Secondary Index

Feature

Primary Index

Secondary Index

Based on

Primary key / clustering key

Any other column

Uniqueness

Always unique

Can be non-unique

Physical row order

Affects row order (if clustered)

Does not affect row order

Number per table

Only one (clustered)

Can have multiple

Index type

Usually sparse or dense

Usually dense

⚠️ Limitations:

  • Requires more storage.

  • Must be maintained during updates/inserts/deletes.

  • Can slow down write operations.

✅ When to Use Secondary Index:

Use when:

  • You often search/filter using non-primary columns.

  • You do JOINs, WHERE, ORDER BY, or GROUP BY on those fields.

  • You want to avoid full table scans.

🧠 Summary:

Aspect

Secondary Index

Column Type

Non-primary

Index Type

Usually Dense

Row Order

Unchanged

Purpose

Faster searches on non-key fields

Storage Use

Moderate to High