CODEWITHSUNDEEP
sqlindexing
L Kefalas
L Kefalas

Reputation: 63

Composite indexes in SQL

Let's say I have a table Person:

ID 
Name
Surname
DateOfBirth

And I have this composite index: (Name, Surname)

Do I have to create an implicit index for Name? e.g For a where clause like that:

SELECT * 
FROM Person 
WHERE Name=?

Or it's not needed as the previous index will cover it?

Upvotes: 1

Views: 190

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You don't need a separate index to query the NAME column, if the NAME column is the first column in your composite index. This is because your index is ordered by NAME ASC, SURNAME ASC, so if you want to look up a NAME value, your database can scan only the relevant parts of the index.

However, your index will be pretty useless if you filter by SURNAME only, in case of which the whole index would need to be traversed anyway.

This website explains this nicely: http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

Upvotes: 2

Related Questions