CODEWITHSUNDEEP
databaseindexing
user2652379
user2652379

Reputation: 982

Columns of an index contain all PKs. Not efficient?

For example, TABLE_A has a, b, c columns. a is a PK and indexed as PK_TABLE_A. And there is an index called IDX_TABLE_A that contains b, a in order.

SELECT a, b
FROM TABLE_A
WHERE a = @P1 AND b = @P2

This query will use PK_TABLE_A and b predicate will be ignored.

SELECT a, b
FROM TABLE_A
WHERE b = @P2

This query will use IDX_TABLE_A. But a doesn't have to be indexed. Being an included column will be more efficient.

Are there any reasonable cases IDX_TABLE_A indexes a column?

Upvotes: 0

Views: 51

Answers (1)

Mark Maurice Williams
Mark Maurice Williams

Reputation: 217

Including columns in an index that do not help with locating particular rows can still improve performance of a query, by allowing values for those columns to be retrieved directly from the index record, without following a reference from the index record to the table record to obtain them. Queries whose selected columns are all included in one (or more) indexes are called "covered" queries; an index "covers" all the desired columns and the database does not need to access the table rows themselves to build the query results.

The index on (b,a) in TABLE_A might exist to speed up a query that matches on b, or possibly both b and a (these could be exact matches, range matches or other kinds), and wants to quickly return only the values of b and a in the query results, but not the values of column c.

Upvotes: 1

Related Questions