Database Indexing Basics Interview Questions

Master Database Indexing fundamentals with interview-focused questions covering indexes, index architecture, table scans, index scans, unique indexes, primary key indexes, index maintenance, execution plans, and production best practices.

Introduction

Indexes are one of the most important database optimization techniques. A well-designed index can reduce query execution time from minutes to milliseconds, while a poorly designed index can actually degrade performance.

Almost every SQL database—including MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and SQLite—uses indexes to speed up data retrieval.

Understanding indexes is essential for Backend Developers, Database Engineers, Solution Architects, and anyone preparing for SQL interviews.

Database Index Architecture

flowchart LR

Application --> SQLQuery --> QueryOptimizer --> Index --> TableRows --> Result

1. What is a Database Index?

Answer

A Database Index is a special data structure that allows the database to locate rows quickly without scanning the entire table.

It works similarly to an index in a book.

Instead of reading every page,

↓

Go directly to the required page.

2. Why do databases need indexes?

Without indexes

Database

↓

Read Every Row

↓

Find Matching Row

With indexes

Database

↓

Search Index

↓

Locate Row Directly

Benefits

Faster Reads
Lower CPU
Less Disk IO
Better Scalability

Book Index Analogy

flowchart LR

BookIndex --> PageNumber --> Chapter --> Information

3. What problems do indexes solve?

Indexes improve

Search Performance
Sorting
Filtering
JOIN Operations
GROUP BY
ORDER BY

4. How does an index work?

Steps

Query received
Optimizer checks indexes
Best index selected
Matching rows located
Results returned

Index Lookup Flow

flowchart LR

SQL --> Optimizer --> IndexLookup --> MatchingRows --> Application

5. What is a Table Scan?

A Table Scan reads

every row

inside a table.

Example

1 Million Rows

↓

Read 1 Million Rows

Very expensive.

6. What is an Index Scan?

Database reads

only relevant index entries.

Example

1 Million Rows

↓

Read 20 Index Entries

Much faster.

Table Scan vs Index Scan

flowchart LR

TableScan --> ReadEntireTable

IndexScan --> ReadIndexOnly

7. What is Full Table Scan?

Full Table Scan

Sequential Scan

Database examines every record.

Useful only for

Small Tables
Analytics
Very Low Selectivity

8. What is an Index Seek?

Index Seek directly navigates to matching rows.

Example

SELECT *

FROM Employee

WHERE EmployeeId = 100

Very efficient.

9. Difference between Index Seek and Index Scan?

Index Seek	Index Scan
Direct Lookup	Reads Many Index Pages
Fastest	Slower
Equality Search	Range Search
Minimal IO	More IO

10. What is a Primary Key Index?

Every Primary Key automatically creates an index.

Example

CREATE TABLE Employee(

EmployeeId INT PRIMARY KEY

);

11. What is a Unique Index?

Prevents duplicate values.

Example

CREATE UNIQUE INDEX

idx_email

ON Employee(email);

12. What is a Non-Unique Index?

Allows duplicate values.

Useful for

Department

City

Country

13. Can multiple indexes exist?

Yes.

One table can have

Primary Key Index
Unique Index
Composite Index
Multiple Secondary Indexes

14. Which queries benefit from indexes?

WHERE
JOIN
ORDER BY
GROUP BY
DISTINCT

15. Which queries may not benefit?

Small Tables
Full Table Reports
Low Selectivity Queries

16. What is Index Selectivity?

Selectivity measures

how unique

column values are.

Example

EmployeeId

↓

Very High

Gender

↓

Very Low

Higher selectivity

↓

Better index.

17. Why is high selectivity important?

Example

EmployeeId

1 Million Unique Values

Easy lookup.

Example

Gender

Male

Female

Index not very useful.

18. What happens during INSERT?

Database performs

Insert Row

↓

Update Index

Indexes make inserts slightly slower.

19. What happens during UPDATE?

If indexed columns change

↓

Index also updates.

Extra work required.

20. What happens during DELETE?

Delete row

↓

Remove index entry

↓

Rebalance index if required.

Index Maintenance

flowchart LR

Insert --> UpdateIndex

Update --> UpdateIndex

Delete --> RemoveIndexEntry

21. Do indexes consume storage?

Yes.

Every index requires additional disk space.

Trade-off

More Storage

↓

Faster Reads

22. Do indexes improve writes?

No.

Indexes generally

slow down

INSERT

UPDATE

DELETE

because index structures must also be updated.

23. Why shouldn't every column be indexed?

Too many indexes

↓

More Storage

↓

Slower Writes

↓

Longer Maintenance

24. What is the Query Optimizer?

The Query Optimizer decides

whether

to use

Index
Table Scan
Index Scan
Index Seek

based on estimated cost.

Optimizer Flow

flowchart LR

SQLQuery --> Optimizer

Optimizer --> IndexSeek

Optimizer --> TableScan

25. What is an Execution Plan?

Execution Plan shows

how the database executes a query.

Useful for

Performance Tuning
Index Analysis
Query Optimization

26. How do you check an Execution Plan?

Examples

MySQL

EXPLAIN SELECT ...

PostgreSQL

EXPLAIN ANALYZE

SQL Server

Actual Execution Plan

27. What is a Covering Index?

A covering index contains all columns required by a query.

Benefits

No table lookup
Faster execution

Covered in detail later.

28. Banking Example

Query

SELECT *

FROM Accounts

WHERE AccountId=1001;

Without Index

↓

Scan millions of rows.

With Index

↓

Direct lookup.

29. E-Commerce Example

ProductId

Index

↓

Instant product retrieval.

30. HR Example

Employee Search

EmployeeId

Email

Indexes allow quick employee lookup.

Enterprise Best Practices

Index frequently searched columns.
Avoid indexing every column.
Prefer high-cardinality columns.
Review execution plans regularly.
Remove unused indexes.
Monitor write performance.
Use composite indexes when appropriate.
Keep statistics updated.
Test with production-sized data.
Review indexes after schema changes.

Quick Revision

Topic	Key Point
Index	Speeds Up Queries
Table Scan	Reads Entire Table
Index Scan	Reads Index
Index Seek	Direct Lookup
Primary Key Index	Automatic
Unique Index	Prevents Duplicates
Non-Unique Index	Allows Duplicates
High Selectivity	Better Performance
Query Optimizer	Chooses Best Plan
Execution Plan	Shows Query Execution

Interview Tips

Interviewers frequently ask

What is an index?
Why are indexes needed?
Table Scan vs Index Scan.
Index Seek vs Index Scan.
Do indexes improve writes?
Why not index every column?
What is index selectivity?
What is an execution plan?
How does the optimizer choose an index?
Give a real production example where indexing improved performance.

Always explain both advantages and trade-offs. Mention that indexes significantly improve read performance but increase storage usage and slow write operations because the index must be maintained.

Summary

Database indexes are specialized data structures that dramatically improve query performance by reducing the amount of data that must be scanned. They enable efficient searching, filtering, sorting, and joining of data while allowing databases to scale to millions or billions of records.

Understanding index architecture, index scans, index seeks, execution plans, selectivity, and index maintenance is fundamental for database performance tuning and is one of the most important topics in SQL, backend engineering, and solution architect interviews.