An index seek is the fastest way a database can use an index to find data. When you perform a seek, the database jumps directly to the exact location in the index where your data lives, grabs what it needs, and moves on. No scanning, no reading through irrelevant entries. Just a precise lookup using the index’s sorted structure.
This is fundamentally different from an index scan, where the database reads through the index sequentially. Seeks are only possible when your query conditions allow the database to pinpoint specific index entries without examining others.
How Index Seeks Work
Indexes are organized in a sorted structure, typically a B-tree (or B+ tree in some implementations like SQL Server for rowstore indexes). When you search for a specific value, the database can navigate this tree efficiently, making comparisons at each level to narrow down to the exact location. It’s like using a phone book – you don’t start at page one and read every entry. You flip to the right section because you know the book is alphabetically sorted.
For an index seek to happen, your query needs to provide conditions that match the index’s leading column(s) with specific values. The most common case is an equality condition like WHERE artist = 'Tool'. If you have an index on the artist column, the database knows exactly where “Tool” sits in the sorted index and can jump straight there.
Range conditions on the leading index column can also enable seeks, though they’ll seek to the start of the range and then read sequentially until the range ends. It’s still more efficient than scanning the entire index.
When Do Index Seeks Happen?
Index seeks require your query to provide specific, usable conditions. Equality operators (=) on indexed columns are ideal. You might also see seeks with IN clauses when the list of values is small. In this case, the database may perform multiple seeks, one for each value.
Composite indexes (indexes on multiple columns) can enable seeks, but only if your query conditions include the leftmost columns of the index. An index on (venue_name, show_date) can seek when you filter by venue, or by venue and date together, but not when you filter only by date.
But the database won’t seek if your query uses functions or calculations on the indexed column. A query with WHERE YEAR(show_date) = 2026 prevents seeking because the database would need to evaluate the function for each index entry. But WHERE show_date >= '2026-01-01' AND show_date < '2027-01-01' can seek because it compares the raw column value.
Examples of Index Seeks
The following examples show index seeks in action. We’ll look at how a few different DBMSs indicate index seeks in their query execution plans.
While the conceptual mechanism of an index seek is generally the same across various DBMSs, the underlying implementation, specific data structures, and performance implications vary significantly among them. Their query plans can differ a lot too. So we’ll look at the query plans of SQL Server, MySQL, and PostgreSQL.
SQL Server
We’ll first create and populate a table. Then we’ll create some indexes. We’ll then check the query plan for a couple of queries that use the indexes.
Create sample data:
CREATE TABLE concert_tickets (
ticket_id INT PRIMARY KEY,
venue_name VARCHAR(100),
artist VARCHAR(100),
show_date DATE,
price DECIMAL(10,2),
section VARCHAR(20)
);
CREATE INDEX idx_artist ON concert_tickets(artist);
CREATE INDEX idx_venue_date ON concert_tickets(venue_name, show_date);
-- Generate sample data using a recursive CTE
WITH numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10000
)
INSERT INTO concert_tickets
SELECT
n,
CASE (n % 5)
WHEN 0 THEN 'Red Rocks'
WHEN 1 THEN 'Greek Theatre'
WHEN 2 THEN 'Hollywood Bowl'
WHEN 3 THEN 'Madison Square Garden'
ELSE 'The Fillmore'
END,
CASE (n % 8)
WHEN 0 THEN 'The Midnight'
WHEN 1 THEN 'Khruangbin'
WHEN 2 THEN 'Heilung'
WHEN 3 THEN 'Tool'
WHEN 4 THEN 'Gojira'
WHEN 5 THEN 'Explosions in the Sky'
WHEN 6 THEN 'Sigur Rós'
ELSE 'Godspeed You! Black Emperor'
END,
DATEADD(DAY, (n % 365), '2026-01-01'),
50.00 + (n % 150),
CASE (n % 4)
WHEN 0 THEN 'GA'
WHEN 1 THEN 'Orchestra'
WHEN 2 THEN 'Balcony'
ELSE 'Floor'
END
FROM numbers
OPTION (MAXRECURSION 10000);
GONow that we have the data and indexes, we can go ahead and query it. But for this article, we’re interested in seeing SQL Server’s estimated query plan rather than the actual query results. So we’ll use the SET SHOWPLAN_TEXT command to enable (and disable) the query plan output:
SET SHOWPLAN_TEXT ON;
GO
SELECT artist, show_date, section
FROM concert_tickets
WHERE venue_name = 'Red Rocks'
AND show_date = '2026-07-15';
GO
SET SHOWPLAN_TEXT OFF;
GOOutput:
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[ticket_id], [Expr1003]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[idx_venue_date]), SEEK:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[venue_name]='Red Rocks' AND [db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[show_date]='2026-07-15') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[PK__concert___D596F96B9471E8D0]), SEEK:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[ticket_id]=[db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[ticket_id]) LOOKUP ORDERED FORWARD)
This shows an “Index Seek” on idx_venue_date with the exact conditions. You can see SEEK:([venue_name]='Red Rocks' AND [show_date]='2026-07-15'). The “Nested Loops” and “Clustered Index Seek” that follow are SQL Server looking up the additional columns (artist, section) from the main table since they’re not in the index.
Now let’s try another query:
SET SHOWPLAN_TEXT ON;
GO
SELECT artist, show_date, price
FROM concert_tickets
WHERE artist = 'Tool';
GO
SET SHOWPLAN_TEXT OFF;
GOOutput:
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[PK__concert___D596F96B9471E8D0]), WHERE:([db_4462q5hnj_446br3chf].[dbo].[concert_tickets].[artist]='Tool'))
StmtText
-------------------------------------------------------------------------------------------------------------------------
This shows a “Clustered Index Scan”. It’s actually doing a table scan using the clustered index (primary key). SQL Server decided not to use the idx_artist index at all. This might be because the optimizer thinks returning 1,250 rows (about 12.5% of the table) makes a table scan more efficient than seeking on the non-clustered index and then looking up the full rows.
So we can see that, just because the database can do an index seek, doesn’t mean that it will. Sometimes the query optimizer will choose a different route, depending on various factors.
MySQL
Now let’s try MySQL to see how it reports on index seeks.
Create/populate the table and create two indexes:
CREATE TABLE concert_tickets (
ticket_id INT PRIMARY KEY,
venue_name VARCHAR(100),
artist VARCHAR(100),
show_date DATE,
price DECIMAL(10,2),
section VARCHAR(20)
);
CREATE INDEX idx_artist ON concert_tickets(artist);
CREATE INDEX idx_venue_date ON concert_tickets(venue_name, show_date);
-- Generate sample data
INSERT INTO concert_tickets
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10000
)
SELECT
n,
CASE (n % 5)
WHEN 0 THEN 'Red Rocks'
WHEN 1 THEN 'Greek Theatre'
WHEN 2 THEN 'Hollywood Bowl'
WHEN 3 THEN 'Madison Square Garden'
ELSE 'The Fillmore'
END,
CASE (n % 8)
WHEN 0 THEN 'The Midnight'
WHEN 1 THEN 'Khruangbin'
WHEN 2 THEN 'Heilung'
WHEN 3 THEN 'Tool'
WHEN 4 THEN 'Gojira'
WHEN 5 THEN 'Explosions in the Sky'
WHEN 6 THEN 'Sigur Rós'
ELSE 'Godspeed You! Black Emperor'
END,
DATE_ADD('2026-01-01', INTERVAL (n % 365) DAY),
50.00 + (n % 150),
CASE (n % 4)
WHEN 0 THEN 'GA'
WHEN 1 THEN 'Orchestra'
WHEN 2 THEN 'Balcony'
ELSE 'Floor'
END
FROM numbers;MySQL doesn’t have a SET SHOWPLAN_TEXT option like SQL Server does. Instead, MySQL has the EXPLAIN command. Prefixing a query with EXPLAIN allows us to view the query execution plan:
EXPLAIN SELECT artist, show_date, price
FROM concert_tickets
WHERE artist = 'Tool';Output:
+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | concert_tickets | NULL | ref | idx_artist | idx_artist | 403 | const | 1250 | 100.00 | NULL |
+----+-------------+-----------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
MySQL doesn’t explicitly state that whether or not it’s an “index seek”. But we can determine that it’s a seek when the type column shows ref. This execution plan shows type: ref, which indicates an index seek.
MySQL is using the idx_artist index to jump directly to the entries where artist equals Tool. The ref: const means it’s comparing against a constant value. It estimates finding 1,250 matching rows, but the main point is that it’s not reading through the entire index – it’s seeking to the specific location where Tool appears.
Here’s an example that uses the composite index:
EXPLAIN SELECT artist, show_date, section
FROM concert_tickets
WHERE venue_name = 'Red Rocks'
AND show_date = '2026-07-15';Output:
+----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | concert_tickets | NULL | ref | idx_venue_date | idx_venue_date | 407 | const,const | 27 | 100.00 | NULL |
+----+-------------+-----------------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
This also shows type: ref, confirming an index seek. MySQL uses the idx_venue_date composite index and seeks based on both columns. We can see this with the ref: const,const part, which indicates two constant values (venue name and date). The database jumps directly to the index entries matching both Red Rocks and the specific date 2026-07-15, estimating 27 matching rows. Because the query provides equality conditions for both columns in the index, MySQL can seek precisely to that combination.
PostgreSQL
PostgreSQL doesn’t use the term “Index seek” either. It uses the term “Index Scan” for both seeks and scans, which can be confusing if you’re coming from a database like SQL Server. In PostgreSQL, to determine which operation is actually happening, you need to look at the index condition in the execution plan.
When you see a plain “Index Scan” with specific equality conditions, it’s performing a seek operation:
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using idx_venue_date on concert_tickets (cost=0.28..8.30 rows=1 width=280)
Index Cond: (((venue_name)::text = 'Red Rocks'::text) AND (show_date = '2026-07-15'::date))
(2 rows)
This is seeking directly to rows matching both conditions. This is the PostgreSQL equivalent of MySQL’s type: ref.
However, you might also see “Bitmap Index Scan” in the execution plan:
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on concert_tickets (cost=4.33..26.84 rows=7 width=238)
Recheck Cond: ((artist)::text = 'Tool'::text)
-> Bitmap Index Scan on idx_artist (cost=0.00..4.33 rows=7 width=0)
Index Cond: ((artist)::text = 'Tool'::text)
(4 rows)
This is still a seek operation in terms of how it locates data, but PostgreSQL uses a two-phase approach. It first builds a bitmap of matching row locations, then fetches those rows in an optimized order. The “Bitmap Heap Scan” step that follows shows the actual data retrieval.
The main indicator is the index condition. If it shows equality operators (=) or specific value lookups on the indexed columns, the database is seeking to precise locations rather than scanning through the index sequentially.
Index Seeks vs Index Scans
The main difference between seeks and scans comes down to precision. Seeks jump to specific locations, whereas scans read sequentially. Seeks are faster because they examine fewer index entries, but they’re only possible when your query conditions allow the database to calculate exactly where to look.
A query like WHERE artist = 'Tool' enables a seek. The database navigates the index tree directly to “Tool”. But WHERE artist LIKE '%Tool%' forces a scan because the wildcard at the beginning means “Tool” could appear anywhere in the artist name. The index’s sorted order doesn’t help, so the database has to check every entry.
Performance differences are most noticeable on large tables. Seeking through an index on a million-row table might examine a handful of index pages. Scanning that same index could read thousands of pages. On small tables, the distinction is much less of an issue.
Getting the Most from Index Seeks
If you want your queries to use seeks, structure them to match your indexes. Use equality conditions on indexed columns where possible. Avoid wrapping indexed columns in functions – WHERE show_date >= '2026-06-01' can seek, but WHERE MONTH(show_date) = 6 cannot (the later would be considered non-SARGable).
For composite indexes, remember that column order matters. An index on (venue_name, show_date) helps queries that filter by venue, or venue and date together. It won’t help a query that only filters by date because that’s not the leftmost column.
Sometimes you can’t restructure a query to enable seeks – maybe you genuinely need a range, or you’re searching with wildcards. That’s fine. Index seeks are a tool for specific situations, not a goal to chase in every query. Use them when they fit your access patterns, and don’t worry when they don’t.