Database Index Best Practices Interview Questions

Master Database Index Best Practices with interview-focused questions covering execution plans, slow query analysis, index fragmentation, cardinality, selectivity, duplicate indexes, over-indexing, index maintenance, monitoring, and production troubleshooting.

Introduction

Indexes can dramatically improve query performance, but poor indexing strategies are one of the leading causes of production database problems.

Common issues include

Slow Queries
High CPU Usage
Excessive Disk IO
Deadlocks
Long Insert Times
Large Storage Consumption
Poor Execution Plans

Understanding indexing best practices is critical for Backend Developers, Database Engineers, Performance Engineers, and Solution Architects.

Database Performance Tuning Workflow

flowchart LR

SlowQuery --> ExecutionPlan --> IndexAnalysis --> Optimization --> PerformanceTesting --> Production

1. What are Index Best Practices?

Answer

Index Best Practices are guidelines that help design indexes for

Faster Queries
Lower Storage
Better Write Performance
Lower Maintenance Cost

2. What is the biggest indexing mistake?

Creating indexes

without understanding

Application Query Patterns.

Always design indexes based on

WHERE
JOIN
ORDER BY
GROUP BY

3. Should every column be indexed?

No.

Too many indexes

↓

Slower Inserts

↓

Higher Storage

↓

Longer Maintenance

Only index frequently searched columns.

4. What is Over-Indexing?

Creating unnecessary indexes.

Problems

Slower Writes
More Storage
Longer Backups
Higher Maintenance

5. What is Under-Indexing?

Missing indexes required for frequently executed queries.

Symptoms

Table Scan
High CPU
Slow Queries

6. How do you identify missing indexes?

Tools

Execution Plan
EXPLAIN
Query Analyzer
Performance Dashboard

Execution Plan

flowchart LR

SQL --> Optimizer --> ExecutionPlan

ExecutionPlan --> IndexSeek

ExecutionPlan --> TableScan

7. What is an Execution Plan?

Execution Plan shows

how the database executes a query.

Useful for

Performance Analysis
Index Verification
Query Optimization

8. How do you analyze slow queries?

Steps

Identify Slow Query
Check Execution Plan
Identify Table Scan
Add Index
Test Again

9. What is EXPLAIN?

Used to analyze SQL execution.

MySQL

EXPLAIN

SELECT *
FROM Employee
WHERE EmployeeId=100;

10. What is EXPLAIN ANALYZE?

PostgreSQL executes the query and returns

Actual Execution Time
Actual Rows
Actual Cost

Useful for accurate tuning.

11. What is Cardinality?

Cardinality represents

Number of unique values

inside a column.

Example

EmployeeId

1,000,000 Unique Values

High Cardinality

12. What is Selectivity?

Selectivity measures

how effectively an index filters rows.

Higher selectivity

↓

Better index performance.

Cardinality vs Selectivity

flowchart LR

HighCardinality --> HighSelectivity --> BetterIndex

13. Which columns should be indexed?

Good candidates

Primary Keys
Foreign Keys
Frequently Filtered Columns
JOIN Columns
ORDER BY Columns
GROUP BY Columns

14. Which columns should NOT be indexed?

Avoid

Boolean Columns
Gender
Status
Frequently Updated Columns
Large Text Columns

Unless required by queries.

15. What is Index Fragmentation?

Pages become scattered over time.

Causes

Inserts
Updates
Deletes
Page Splits

Fragmentation

flowchart LR

SequentialPages --> FragmentedPages --> SlowReads

16. How do you reduce fragmentation?

Rebuild Index
Reorganize Index
Use Sequential Keys
Monitor Fragmentation

17. Difference between Rebuild and Reorganize?

Rebuild	Reorganize
Recreates Index	Defragments Existing Index
Faster Reads	Online Operation
More Resource Intensive	Lower Impact

18. What are Duplicate Indexes?

Indexes with nearly identical definitions.

Example

(EmployeeId)

(EmployeeId, Name)

Review whether both are necessary.

19. Why should duplicate indexes be removed?

Benefits

Lower Storage
Faster Writes
Easier Maintenance

20. What are Unused Indexes?

Indexes never selected by the optimizer.

Problems

Consume Storage
Slow Writes
Waste Resources

21. How do you identify unused indexes?

Database tools

SQL Server

DMVs

PostgreSQL

pg_stat_user_indexes

MySQL

Performance Schema
sys schema

22. What is Index Maintenance?

Regular activities

Rebuild
Reorganize
Update Statistics
Remove Unused Indexes

23. Why are statistics important?

Statistics help the Query Optimizer choose the best execution plan.

Outdated statistics

↓

Poor execution plans

↓

Slow queries.

24. What is Index Statistics?

Statistics include

Row Count
Data Distribution
Cardinality
Histogram

25. What is a Histogram?

Histogram shows value distribution.

Optimizer uses it to estimate

Expected Rows.

Optimizer Decision

flowchart LR

Statistics --> Optimizer --> ExecutionPlan

26. What is a Slow Query Log?

Captures queries exceeding a configured execution time.

Useful for

Performance Analysis
Index Improvements
Capacity Planning

27. Banking Example

Slow Query

SELECT *

FROM Transactions

WHERE AccountNumber=?

Execution Plan

↓

Table Scan

Created Index

(AccountNumber)

Execution Time

4 Seconds

↓

20 Milliseconds

28. E-Commerce Example

Slow Search

WHERE ProductName=?

Created

(ProductName)

Response improved dramatically.

29. HR Example

Employee Lookup

WHERE Email=?

Unique Index

↓

Instant lookup.

30. Logging Example

Query

WHERE Application='Payments'

AND LogDate>'2026-01-01'

Composite Index

(Application,

LogDate)

Improved reporting performance.

31. What are Write Performance Trade-offs?

More indexes

↓

More work during

INSERT
UPDATE
DELETE

Balance

Read Performance

Write Performance.

32. How do Composite Indexes reduce indexes?

Instead of

Department

Salary

Create

(Department,

Salary)

One index supports multiple queries.

33. Why should production workloads be tested?

Small datasets

↓

Hide performance problems.

Always test with

Production-scale data.

34. Monitoring Metrics

Monitor

Slow Queries
CPU Usage
Disk IO
Buffer Cache
Fragmentation
Index Usage
Missing Indexes
Deadlocks

Monitoring Workflow

flowchart LR

Monitoring --> SlowQueries --> ExecutionPlan --> Optimization --> Validation

Enterprise Best Practices

Design indexes around query patterns.
Prefer high-cardinality columns.
Keep indexes narrow.
Avoid duplicate indexes.
Remove unused indexes regularly.
Monitor fragmentation.
Update statistics frequently.
Review execution plans for critical queries.
Test with production-sized datasets.
Balance read and write performance.

Quick Revision

Topic	Best Practice
Query Analysis	EXPLAIN
PostgreSQL	EXPLAIN ANALYZE
High Cardinality	Preferred
Low Cardinality	Avoid
Duplicate Indexes	Remove
Unused Indexes	Remove
Fragmentation	Monitor
Statistics	Keep Updated
Composite Index	Reduce Multiple Indexes
Slow Query Log	Enable

Interview Tips

Interviewers frequently ask

How do you optimize slow SQL queries?
How do you identify missing indexes?
What is cardinality?
What is selectivity?
Difference between rebuild and reorganize.
Why are statistics important?
What causes fragmentation?
Why shouldn't every column be indexed?
How do you identify unused indexes?
Give a production performance tuning example.

Always explain a structured troubleshooting approach:

Identify the slow query.
Review the execution plan.
Check index usage.
Optimize indexes or SQL.
Validate performance after changes.

This demonstrates real production experience.

Summary

Effective indexing is not about creating as many indexes as possible—it is about creating the right indexes based on application access patterns. Proper index design, combined with execution plan analysis, statistics maintenance, fragmentation management, and continuous monitoring, ensures scalable database performance.

Mastering execution plans, cardinality, selectivity, composite indexes, index maintenance, and production troubleshooting is essential for SQL performance tuning and is one of the most valuable skills for Backend Developers, Database Engineers, DBAs, and Solution Architects.