SQL Notes
Learn SQL Indexing Strategies in detail, understand index design, choosing the right indexes, composite indexes, covering indexes, filtered indexes, and performance optimization.
In the previous lessons, you learned about:
Query Optimization
EXPLAIN Plan
Execution Planssome 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 IndexesSimple Definition
An Indexing Strategy is when you 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 Scan
With 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 Scan
With 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 Salary
may not be optimal.
Better:
CREATE INDEX IX_DepartmentSalary
ON Employees
(
DepartmentID,
Salary
);What\'s a? Composite Index?
A composite index includes multiple columns.
Example:
CREATE INDEX IX_Employee
ON Employees
(
DepartmentID,
Salary
);Leftmost Prefix Rule
interview favorite.
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 Required
What\'s 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 Integrity
Strategy 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 Storage
Strategy 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 Joins
Example:
Email
DepartmentID
CategoryIDWhen NOT to Create Indexes
Not every column should be indexed.
Avoid indexing:
Small Tables Frequently Updated Columns Low Selectivity Columns
Example: 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 Indexes
Consequences:
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\'s an? Indexing Strategy?
A planned approach for creating and managing indexes.
What\'s a? Composite Index?
An index containing multiple columns.
What\'s a? Covering Index?
An index that contains all columns required by a query.
What\'s 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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, optimization, indexing, strategies
Related SQL Complete Guide Topics