Composite indexes in SQL

Question

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?

No, you do not need another index. Your composite index covers it. — Gordon Linoff
– Gordon Linoff, Commented Mar 13, 2016 at 18:33
Gordon is right. But if you want proof, check the execution plan. — user330315
– user330315, Commented Mar 13, 2016 at 18:37

Lukas Eder · Accepted Answer · 2016-03-13 19:55:30Z

2

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

edited Mar 13, 2016 at 19:55

answered Mar 13, 2016 at 19:50

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jleach Over a year ago

His book is highly recommended (SQL Performance Explained, Markus Winand)

Collectives™ on Stack Overflow

Composite indexes in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related