Learning of SQL Day 39

 

Day 39: Advanced Indexing Techniques

Introduction:

• Advanced indexing techniques can significantly improve the performance and efficiency of your database queries. These techniques include the use of composite indexes, covering indexes, and filtered indexes.

Key Concepts:

• Composite Index: An index on multiple columns.

• Covering Index: An index that contains all the columns needed by a query, allowing the query to be satisfied entirely by the index without accessing the table.

• Filtered Index: An index that includes only a subset of rows in a table, based on a specified condition.

SQL Commands and Examples:

1. Creating a Composite Index:

   • Create an index on multiple columns to improve query performance involving those columns.

sql

-- Create a composite index on the LastName and FirstName columns in the Students table
CREATE INDEX idx_name ON Students(LastName, FirstName);

2. Creating a Covering Index:

   • Create an index that covers all columns needed by a query.

sql

-- Create a covering index on the StudentID, LastName, and Age columns in the Students table
CREATE INDEX idx_covering ON Students(StudentID, LastName, Age);

3. Creating a Filtered Index:

   • Create an index that includes only a subset of rows based on a condition.

sql

-- Create a filtered index on the Age column for students older than 18
CREATE INDEX idx_filtered_age ON Students(Age)
WHERE Age > 18;

4. Using Advanced Indexes in Queries:

   • Write queries that benefit from composite, covering, and filtered indexes.

sql

-- Query that benefits from the composite index on LastName and FirstName
SELECT StudentID, LastName, FirstName
FROM Students
WHERE LastName = 'Smith' AND FirstName = 'John';

-- Query that benefits from the covering index on StudentID, LastName, and Age
SELECT StudentID, LastName, Age
FROM Students
WHERE StudentID = 1;

-- Query that benefits from the filtered index on Age
SELECT StudentID, LastName, Age
FROM Students
WHERE Age > 18;

Practice Exercise:

1. Use the Students table with columns StudentID, FirstName, LastName, Age.

2. Write a query to create a composite index on the FirstName and LastName columns.

3. Write a query to create a covering index on the StudentID, FirstName, and LastName columns.

4. Write a query to create a filtered index on the Age column for students older than 20.

5. Write a query that benefits from each of these advanced indexes.

sql

-- Create a composite index on the FirstName and LastName columns in the Students table
CREATE INDEX idx_composite_name ON Students(FirstName, LastName);

-- Create a covering index on the StudentID, FirstName, and LastName columns in the Students table
CREATE INDEX idx_covering_student ON Students(StudentID, FirstName, LastName);

-- Create a filtered index on the Age column for students older than 20
CREATE INDEX idx_filtered_age ON Students(Age)
WHERE Age > 20;

-- Query that benefits from the composite index on FirstName and LastName
SELECT StudentID, FirstName, LastName
FROM Students
WHERE FirstName = 'Alice' AND LastName = 'Johnson';

-- Query that benefits from the covering index on StudentID, FirstName, and LastName
SELECT StudentID, FirstName, LastName
FROM Students
WHERE StudentID = 2;

-- Query that benefits from the filtered index on Age
SELECT StudentID, FirstName, LastName, Age
FROM Students
WHERE Age > 20;

Important Tips:

• Use composite indexes to optimize queries that filter or sort on multiple columns.

• Covering indexes can significantly improve performance by allowing queries to be satisfied entirely by the index.

• Filtered indexes are useful for optimizing queries that filter on specific conditions, reducing the index size and improving performance.

Understanding and implementing advanced indexing techniques can greatly enhance the performance and efficiency of your database queries.