• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Database Design - Creating Index


I want to create some index on a specific table to help boost performance for data retrieval.  The data would be searched on field1 and also on field2:

For example:
1) Where field1 = 1
2) Where field2 > 300
3) Where field1 = 20 and field2 > 400

Also, the two fields would be included in the order by clause (e.g. "order by field1, field2").

Should creating an index on field1 and a separate index on field2 is enough to take care everything stated above?  Or do I also have to create a combined index of (field1, field2) besides creating a separate index for field1 and field2?  What is the best approach for creating index in the above scenario?

  • 2
2 Solutions
Scott PletcherSenior DBACommented:
Separate indexes will likely be better if you use the command "WHERE col2 > value" fairly frequently.  If that search is rare, you should probably combine them as (col1, col2).

Either way col1 should probably be clustered.
I suggest creating an index on field1 by itself and another index on field2, field1 in that order.  So if the query only specified field1, it will use the first index.  If the query specified only field2, then it will use the second index.  And if both fields are specified, the second index will also be used.
Scott PletcherSenior DBACommented:
Actually if you cluster field1 you won't explicitly need to add it to the index on field2 -- SQL will automatically add it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now