What is index scan vs index seek?
SQL indexes are special lookup tables that the database search engine can use to speed up data retrieval. They are crucial for optimizing query performance. Understanding the difference between an index seek and an index scan is key to writing efficient SQL queries.
Understanding SQL Indexes
An index in SQL is a performance tuning method that allows for faster retrieval of records from a database table. It works by creating an entry for each value in the indexed columns, similar to an index in a book, pointing to the location of the corresponding data. This structure helps the database engine locate rows much more quickly than scanning the entire table.
Index Seek
An index seek is a highly efficient operation used to locate specific rows based on an index. It's akin to finding a specific page in a book using its index. The database engine traverses the index structure (typically a B-tree) to quickly pinpoint the exact data pages containing the desired records. This operation is very fast, often taking logarithmic time relative to the number of index entries.
Index seeks are typically performed when a WHERE clause uses an equality operator (=) or a very narrow range (<, >, <=, >=) on an indexed column, especially when the leading column of a composite index is used. It's the most desirable index operation for performance.
SELECT CustomerName FROM Customers WHERE CustomerID = 123;Index Scan
An index scan is a less precise operation where the database engine reads through a portion or all of an index to find the requested data. Instead of pinpointing exact locations, it effectively "scans" across a range of index entries in sequential order. This operation is less efficient than a seek because it has to read more data, though it's still generally faster than a full table scan.
Index scans occur when a query requires retrieving a range of values, or when the WHERE clause conditions cannot be fully satisfied by an index seek (e.g., using LIKE '%value', non-leading columns in a composite index, or expressions on indexed columns). In some cases, if the index covers all columns needed by the query (a covering index), an index scan might still be efficient as it avoids accessing the base table.
SELECT OrderID, OrderDate FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';Key Differences and Performance Implications
| Feature | Index Seek | Index Scan |
|---|---|---|
| Operation Type | Precise lookup | Sequential traversal |
| Specificity | Highly specific (equality, narrow range) | Broader range, partial matches, or full index traversal |
| Efficiency | Very high (logarithmic time) | Lower than seek (linear time over scanned range) |
| Data Retrieved | Minimal, only matching rows | Potentially many rows, or all rows in the scanned portion of the index |
| Use Case | Targeted lookups on indexed columns | Range queries, LIKE patterns, non-leading index columns, or covering index when full scan is needed |
Conclusion
In summary, an index seek is a surgical operation for finding specific data quickly, while an index scan is a broader sweep across a range of index entries. The SQL optimizer determines which operation to use based on the query structure, available indexes, and data distribution. Understanding these operations is fundamental for diagnosing and improving database query performance by ensuring your queries encourage index seeks whenever possible for critical lookups.