In this lesson, you will learn how to supercharge your query performance by using "Covering Indexes" to eliminate unnecessary trips to the main data table.
The Problem: The "Bookmark Lookup"
When you query a database, the engine typically performs two steps. First, it searches the index to find the location of the rows that match your criteria. Second, if your SELECT statement asks for columns not included in that index, the engine must perform a "Bookmark Lookup." This means it physically jumps to the "heap"—the main table storage—to fetch those extra columns.
If you are querying a large set, these thousands of tiny jumps back and forth between the index and the heap create massive I/O (Input/Output) overhead, slowing your set operations significantly.
The Solution: The Covering Index
A Covering Index is an index that includes all the columns referenced in your query. Because the index itself holds all the necessary data, the database engine never has to look at the heap. It satisfies the entire request from the index structure alone.
Imagine a library index card that not only tells you where a book is located but also contains the full text of the book printed on the back. You would never need to walk to the bookshelf!
Consider this query:
SELECT employee_name, department_id
FROM employees
WHERE salary > 50000;
If your index is only on salary, the engine must find the salary in the index and then fetch the employee_name and department_id from the heap. However, if you create a covering index:
CREATE INDEX idx_salary_coverage
ON employees (salary, employee_name, department_id);
Now, the database finds the records meeting the salary criteria and immediately retrieves the employee_name and department_id directly from the index nodes. By keeping the "set" data physically close together in the index, you reduce the physical I/O operations and make your set-based logic run much faster.
Key Takeaway: A covering index contains all the data required for a query, allowing the database to satisfy the request without ever needing to perform an expensive lookup in the main table.