Understanding Index Seek vs. Index Scan in Databases

Key takeaways

Index seek and index scan are database operations that use indexes to retrieve the data you request in a query. Here are some important facts to know: 

• The query optimizer chooses between an index seek and an index scan based on the estimated cost of each operation.

• An index seek tends to be faster and less costly for highly selective queries, while an index scan can be more effective when you need to look through most of the rows.

• You can strengthen your database skills and learn how to optimize SQL queries for performance across platforms.

Learn how the query optimizer decides between seeks and scans and how indexing strategies influence performance. If you're ready to start building in-demand data engineering skills, enroll in the IBM Data Engineering Professional Certificate. You'll have the opportunity to design a relational database, query and analyze data, and practice using SQL and earn a shareable credential in as little as six months.

Index seek vs. index scan

When you run a database query, the optimizer may choose an index seek or an index scan to find the requested data. The optimizer evaluates each option and selects the plan it thinks will take the least effort, using the fewest reads, calculations, and steps to get your data. An index seek tends to be more efficient for highly selective queries that return a small number of rows, while an index scan can be faster when the execution engine needs to read many or most of the rows.

What is an index seek?

An index seek is a process the database uses to quickly find specific rows that match the conditions of your query without reading the entire table or index. Instead, it moves directly to where the matching data begins and ends, retrieving only the information you request.

In the following example, the query filters for a single customer ID, which allows the database to use the index to find that customer's matching row instead of scanning every record.

SELECT *

FROM Customers

WHERE CustomerID = 10248;

If an index exists on the Customer ID column, the database engine can go straight to the matching row instead of reading unnecessary data. Since it retrieves only relevant records, an index seek typically runs faster and uses fewer resources than a scan. When no suitable index is available, or when the filter doesn't match the indexed column, the database has to scan instead.

Advantages of an index seek

An index seek improves performance by allowing the database to locate specific rows quickly instead of reading through an entire table. It is most useful when you're searching for just a few specific rows in a very large table. The following advantages show how an index seek contributes to faster and more efficient data retrieval:

• Finds specific data faster

• Uses fewer system resources

• Works well for selective inquiries

• Improves query performance

Disadvantages of using an index seek

A seek can only happen when the column used in the filter has an index that the optimizer can use. Whether a seek works depends on how you write the query, what the index covers, and how many indexes you maintain. These factors can add extra work for the database. The following disadvantages indicate why an index seek isn't always the best choice.

• Requires an existing index

• Less efficient for large result sets

• Depends on query design and coverage

• Increases index maintenance

What is the difference between table scan, index scan, and index seek?

During a table scan, the database reads every row in a table. This approach can be slow and resource-intensive for large data sets because it generates many disk I/O operations.

An index scan reads all rows in an index instead of the full table. It's often faster than a table scan when the index is smaller, but it still processes every entry.

An index seek is generally the fastest way to retrieve data when you want just a few specific results. The database engine relies on the index to jump directly to the relevant rows, skipping those that don't match the query.

What is an index scan?

An index scan reads all or most of the entries in an index to find rows that match a query's conditions. Instead of going directly to the matching keys, the database engine starts at the beginning of the index and reads through it sequentially. For example, if your query requests all customer records, the engine will scan through the entire index, reading each entry in order. If the column isn't indexed, it must scan the entire table.

The optimizer typically uses a scan when many rows match the query, when no suitable index supports the filter, or when reading the full index is faster than performing multiple seeks. While an index scan can take advantage of the index's order, it usually consumes more I/O and CPU resources than a seek. For this reason, it's less efficient for highly selective queries but effective when the database must read most of the table anyway.

Clustered index scan vs. nonclustered index seek

A clustered index scan reads all rows in a table's clustered index, which arranges data based on the index key values. Because it touches every page of the clustered index, it can be slower for selective queries but efficient when it needs most of the rows or when results must be returned in key order.

A nonclustered index seek uses a separate index structure to locate only the rows that meet the query's conditions. It retrieves data efficiently by following pointers to the clustered index or base table only when extra columns are required.

Advantages of using an index scan

An index scan can outperform multiple seeks when the query needs to read a large portion of a table or when the results must follow the index's order. It's often a highly effective option for analytical or reporting workloads. The following advantages show how an index scan supports broader data retrieval:

• Reads large data sets efficiently

• Preserves sort order for indexed columns

• Handles analytical or range queries effectively

• Reduces table lookups for covering queries

Disadvantages of using an index scan

While scans can handle broad queries well, they generally use more system resources than seeks. Scans evaluate more rows than necessary, which can increase processing time as tables grow. The following disadvantages show why scans are less efficient for selective queries:

• Reads unnecessary rows

• Uses more I/O and CPU resources 

• Performs poorly on selective filters

• Slows performance as data volume grows

Who typically uses an index seek and index scan?

Database professionals, data analysts, and database developers interact with index seeks and scans as part of managing and optimizing database performance. If you write queries or manage databases, you don't directly choose between a seek and a scan. The optimizer makes that decision based on your query and the available indexes. Your role is to design queries and indexes that help the optimizer choose the most efficient method.

Index scan vs. index seek: Getting started

To see how your database uses index seeks and scans, start by reviewing your query execution plans. Many database management tools show whether a query used a seek or a scan. Experiment with queries that filter on indexed versus non-indexed columns to observe how the optimizer adjusts its plan. These small tests can help you understand how indexing and query design affect performance in real-world workloads.

Read more: How to Learn SQL

Stay current with SQL and database trends

Keep building your database expertise with more resources from our learning community. You can explore tutorials, watch expert insights, and follow the latest discussions in data engineering. Also, subscribe to Career Chat to stay up to date on industry trends or explore these other free resources: 

• Bookmark this page: SQL Glossary

• Watch on YouTube: SQL Starter Pack

• Read this article: Google Sheets Linear Progression: Learn Predictive Modeling

With Coursera Plus, you can learn and earn credentials at your own pace from over 350 leading companies and universities. With a monthly or annual subscription, you'll gain access to more than 10,000 programs—just check the course page to confirm your selection is included.

Build job-ready skills with Coursera Plus

Start 7-day free trial

• 
• 
• 
• 

Start 7-day free trial