In this lesson, you will learn how to design indexes that satisfy query requirements entirely from the index tree, avoiding the performance penalty of retrieving the full data row.
The Cost of the RID Lookup
When you search for data using a secondary index, the database engine finds the index entry quickly within the B-Tree. However, a secondary index usually only contains the column you searched for and a "pointer"—often called a RID (Row Identifier)—that tells the engine where the actual table row lives on the disk.
If your query requests columns that aren't in the index, the engine must perform a RID Lookup. It takes that pointer, jumps to the specific data page in the table, and fetches the full row. Because this often requires a random I/O operation to a different part of the disk, it is significantly slower than reading the index itself.
The Covering Index Solution
A Covering Index is an index designed to "cover" all the columns requested by a specific query. By including all required data within the leaf nodes of the B-Tree index, the database engine never has to leave the index structure to fetch the underlying table row.
Consider a table Employees with columns (ID, LastName, Salary, DepartmentID). If you frequently run this query:
SELECT Salary FROM Employees WHERE LastName = 'Smith';
If you have a standard index on LastName, the engine finds "Smith," takes the RID, and fetches the Salary from the table page. To optimize this, you can create a composite index:
CREATE INDEX idx_salary_lookup ON Employees (LastName, Salary);
Now, the Salary value is stored directly inside the index leaf node alongside LastName. When the engine searches for "Smith," it finds the Salary immediately. The RID Lookup is completely eliminated, turning a slow random-access operation into a fast, sequential-like index scan.
Design Trade-offs
While covering indexes are fast, remember that adding columns to an index increases its size. This consumes more memory in the buffer pool and can slow down write operations (as every index must be updated when data changes). Use them strategically for your most performance-critical "read" queries.
Key Takeaway: A covering index eliminates the expensive RID Lookup by including all requested columns within the index itself, allowing the query to be satisfied entirely from the index leaf nodes.