CODEWITHSUNDEEP
sqldatabase
Alex
Alex

Reputation: 77329

When to use composite indexes?

What are the general rules in regards to using composite indexes? When should you use them, and when should you avoid them?

Upvotes: 19

Views: 12236

Answers (2)

Andomar
Andomar

Reputation: 238068

A query that selects only a few fields can run completely on an index. For example, if you have an index on (OrderId) this query would require a table lookup:

select Status from Orders where OrderId = 42

But if you add a composite index on (OrderId,Status) the engine can retrieve all information it needs from the index.

A sort on multiple columns can benefit from a composite index. For example, an index on (LastName, FirstName) would benefit this query:

select * from Orders order by LastName, FirsName

Sometimes you have a unique constrant on multiple columns. Say for example that you restart order numbers every day. Then OrderNumber is not unique, but (OrderNumber, OrderDayOfYear) is. You can enforce that with a unique composite index.

I'm sure there are many more uses for a composite index, just listing a few examples.

Upvotes: 15

Jose Basilio
Jose Basilio

Reputation: 51468

Composite indexes are useful when your SELECT queries use those columns frequently as criteria in your WHERE clauses. It improves retrieval speed. You should avoid them if they are not necessary.

This article provides some really good information.

Upvotes: 22

Related Questions