SQL Topics
Indexing Strategies
title: Indexing Strategies
In the previous lessons, you learned about:
Query Optimization
EXPLAIN Plan
Execution PlansOne of the most powerful ways to improve SQL performance is:
IndexingHowever:
Creating Indexes Everywhereis not a good strategy.
Poor indexing can:
Increase Storage Usage
Slow INSERT Operations
Slow UPDATE Operations
Slow DELETE OperationsTo achieve optimal performance, databases require:
Indexing Strategieswhich help determine:
Which Columns To Index
When To Use Composite Indexes
When To Avoid Indexes
How To Design Efficient IndexesWhat is an Indexing Strategy?
An Indexing Strategy is a systematic approach to designing and managing indexes to improve database performance.
Instead of randomly creating indexes:
Analyze Queries
Analyze Workload
Design Appropriate IndexesSimple Definition
An Indexing Strategy is the process of selecting and designing indexes to maximize query performance while minimizing storage and maintenance costs.
Why Are Indexing Strategies Important?
Consider:
SELECT *
FROM Employees
WHERE Email =
'abc@gmail.com';Without an index:
Full Table ScanWith an index:
Index SeekPerformance difference:
Milliseconds
vs
Minutesfor very large tables.
How Indexes Work
Imagine a book.
Without an index:
Read Every Pageto find a topic.
With an index:
Jump Directly To Pagecontaining the topic.
Databases work similarly.
Indexes help SQL locate data quickly.
Goals of Indexing
Proper indexing aims to:
Reduce Query Time
Reduce Disk Reads
Improve Scalability
Optimize Joins
Improve FilteringTypes of Indexing Strategies
Common strategies include:
Primary Key Indexing
Foreign Key Indexing
Composite Indexing
Covering Indexing
Filtered Indexing
Unique Indexing
Clustered Indexing
Non-Clustered IndexingStrategy 1: Index Frequently Searched Columns
Columns frequently used in:
WHEREshould usually be indexed.
Example:
SELECT *
FROM Employees
WHERE Email =
'abc@gmail.com';Recommended Index:
CREATE INDEX IX_Email
ON Employees(Email);Strategy 2: Index Join Columns
Joins are very common.
Example:
SELECT *
FROM Employees E
JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Recommended Index:
CREATE INDEX IX_DepartmentID
ON Employees(DepartmentID);Why Join Indexes Matter
Without index:
Large Table ScanWith index:
Efficient LookupStrategy 3: Index Foreign Keys
Foreign keys are frequently used for joins.
Example:
DepartmentID
CustomerID
OrderIDshould often be indexed.
Example
CREATE INDEX IX_CustomerID
ON Orders(CustomerID);Strategy 4: Composite Index
Used when queries filter multiple columns.
Example:
SELECT *
FROM Employees
WHERE DepartmentID = 1
AND Salary > 50000;Single indexes:
DepartmentID
Salarymay not be optimal.
Better:
CREATE INDEX IX_DepartmentSalary
ON Employees
(
DepartmentID,
Salary
);What is a Composite Index?
A Composite Index contains multiple columns.
Example:
CREATE INDEX IX_Employee
ON Employees
(
DepartmentID,
Salary
);Leftmost Prefix Rule
Very important interview topic.
Index:
(DepartmentID, Salary)supports:
WHERE DepartmentID = 1and
WHERE DepartmentID = 1
AND Salary > 50000But may not efficiently support:
WHERE Salary > 50000alone.
Strategy 5: Covering Index
A covering index contains all columns required by a query.
Example:
SELECT
EmployeeID,
EmployeeName
FROM Employees
WHERE DepartmentID = 1;Index:
CREATE INDEX IX_Covering
ON Employees
(
DepartmentID,
EmployeeID,
EmployeeName
);Benefit:
No Table Lookup RequiredWhat is a Covering Index?
An index that contains every column needed by a query.
Advantage
Faster Query Executionbecause SQL retrieves data directly from the index.
Strategy 6: Unique Index
Used when values must remain unique.
Example:
Email
PAN Number
Aadhaar Number
UsernameExample:
CREATE UNIQUE INDEX IX_Email
ON Employees(Email);Benefits
Faster Lookups
Data IntegrityStrategy 7: Filtered Index
Indexes only a subset of rows.
Example:
WHERE IsActive = 1SQL Server Example:
CREATE INDEX IX_ActiveEmployees
ON Employees(EmployeeID)
WHERE IsActive = 1;Benefits
Smaller Index
Better Performance
Less StorageStrategy 8: Clustered Index Strategy
Clustered indexes determine:
Physical Data OrderBest candidates:
Primary Keys
Frequently Sorted ColumnsExample:
EmployeeID
OrderID
TransactionIDStrategy 9: Non-Clustered Index Strategy
Best for:
Search Columns
Filtering
JoinsExample:
Email
DepartmentID
CategoryIDWhen NOT to Create Indexes
Not every column should be indexed.
Avoid indexing:
Small Tables
Frequently Updated Columns
Low Selectivity ColumnsExample: Poor Candidate
GenderValues:
Male
FemaleOnly two values.
Index often provides little benefit.
Why Too Many Indexes Are Bad
Every INSERT:
INSERT INTO Employeesmust update:
Table
+
All Related IndexesConsequences:
Slower Writes
More Storage
Higher MaintenanceIndex Maintenance
Indexes require maintenance.
Tasks include:
Rebuild Indexes
Reorganize Indexes
Update StatisticsFragmentation
Over time indexes become fragmented.
Example:
Page Splits
Random Inserts
Deletescause inefficiencies.
Index Rebuild
Recreates index structure.
Example (SQL Server):
ALTER INDEX IX_Email
ON Employees
REBUILD;Index Reorganize
Lightweight maintenance.
ALTER INDEX IX_Email
ON Employees
REORGANIZE;Real-World Example: Banking
Query:
SELECT *
FROM Transactions
WHERE AccountID = 1001;Recommended Index:
AccountIDReal-World Example: E-Commerce
Query:
SELECT *
FROM Products
WHERE CategoryID = 10;Recommended Index:
CategoryIDReal-World Example: Payroll
Query:
SELECT *
FROM Employees
WHERE DepartmentID = 5;Recommended Index:
DepartmentIDReal-World Example: University
Query:
SELECT *
FROM Students
WHERE RollNumber = 1001;Recommended Index:
RollNumberAdvantages of Good Indexing Strategies
Faster Queries
Major benefit.
Better Join Performance
Improves relationship lookups.
Reduced Disk Reads
Less data scanned.
Better Scalability
Supports large datasets.
Improved User Experience
Faster application response.
Disadvantages
Increased Storage
Indexes consume disk space.
Slower Writes
INSERT, UPDATE, DELETE become more expensive.
Maintenance Required
Indexes need monitoring.
Complexity
Requires understanding of workloads.
Common Mistakes
Indexing Every Column
Creates unnecessary overhead.
Ignoring Composite Indexes
Missed optimization opportunities.
Too Many Indexes
Impacts write performance.
Not Updating Statistics
Poor optimizer decisions.
Ignoring Fragmentation
Reduces efficiency.
Best Practices
Index Frequently Queried Columns
Highest priority.
Index Join Columns
Improves relationships.
Use Composite Indexes Carefully
Follow query patterns.
Monitor Index Usage
Remove unused indexes.
Balance Read and Write Performance
Avoid excessive indexing.
Common Interview Questions
What is an Indexing Strategy?
A planned approach for creating and managing indexes.
What is a Composite Index?
An index containing multiple columns.
What is a Covering Index?
An index that contains all columns required by a query.
What is the Leftmost Prefix Rule?
Composite indexes are most effective when queries start with the first indexed column.
Why shouldn't every column be indexed?
Because indexes increase storage and slow write operations.
Summary
Indexing Strategies help databases achieve optimal performance by carefully selecting and designing indexes. Effective strategies include indexing frequently searched columns, join columns, foreign keys, composite indexes, and covering indexes while avoiding unnecessary indexes that increase maintenance and storage costs.
In this lesson, you learned:
- What Indexing Strategies are
- Composite Indexes
- Covering Indexes
- Unique Indexes
- Filtered Indexes
- Clustered vs Non-Clustered Strategies
- Index Maintenance
- Fragmentation
- Best Practices
Mastering Indexing Strategies is essential because indexes are the foundation of SQL performance optimization.
Next Step
Continue to the next lesson:
Partitioning →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Indexing Strategies.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, optimization, indexing
Related SQL Topics