What's the difference between making an index on 2 columns and an index on each of the columns separately?

Question

I'm new to database indexing, if I have 2 columns in a table that are good choices for indexing like for example,

[Posts](    
   [PostID] [int] IDENTITY(1,1) NOT NULL,
   [UserName] [nvarchar](64) NOT NULL,
   [ApplicationType] [smallint] NOT NULL,
   ...
)

in this case PostID would be the PRIMARY KEY CLUSTERED index, then I want to do more indexing since it's a large table and I want to do on UserName and ApplicationType, now should I index each individually (one on UserName, one on ApplicationType) or index them as a whole (one index on UserName, ApplicationType together)? Is there a limit to the number of indexes I can have before making it bad practice? What generally is the rule of thumb on this?

Thanks,

Ray.

Answer 1

Keep in mind the telephone-book rule for compound indexes: the phone book is effectively indexed by last-name, first-name. It's a compound index.

If you search for people named "Smith, John" then it's helpful that the first-name is part of the index. Once you find the entries with last-name "Smith" then you can find "John" quickly.

But if you need to search for everyone named "John," then the phone book's indexing is not helpful -- you have to search the whole book anyway.

So compound indexes are great if you're searching on the first column named in the index, and optionally the second, etc. But if your search skips the leftmost columns in the index, it's useless for that search.