What is the difference between making an index for 2 columns and an index for each of the columns separately?

Question

I am new to database indexing if I have 2 columns in a table which are good options for indexing, for example

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

in this case, the PostID will be the PRIMARY KEY CLUSTERED index, then I want to do more indexing, since this is a large table, and I want to do it by UserName and ApplicationType, now I have to index each separately (one from UserName, one on ApplicationType) or index them in general (one index in UserName, ApplicationType together)? Is there a limit to the number of indexes I can have before making this a bad practice? Is this usually a rule?

Thanks,

Ray

Answer 1

Keep in mind the phone book rule for compound indexes: the phone book is effectively indexed by first name, last name. This is a composite index.

If you are looking for people named "Smith, John," then it is helpful that the first name be part of the index. When you find records with the last name "Smith", you can quickly find "John."

But if you need to search for everyone by the name "John", then indexing the phone book will not help - you still need to search the entire book.

So complex indexes are great if you are looking for the first column with a name in the index and possibly the second, etc. But if your search skips the leftmost columns in the index, it is useless for this search.

Answer 2

The answer to this question really depends on how you are going to search on the table. If your searches almost always include both columns, then it’s advisable to create an index for both columns. If you will often search in each field independently, it is advisable to create separate indexes for each. In the end, you can have all 3 indexes (one composite, 2 separate columns) - depending on how you search using columns. Think of it like in the phone book - if you are always looking for a last name and first name, you will find what you are looking for. But if you want to search the phone book for everyone with the name Scott, you need a new index that wasn’t (LName, FName). If you want to find everyone with the given last name, you can still do this using an index with multiple columns (LName, FName).

Each database has its own restrictions on the number of indexes per table, the number of columns per index, etc. They are usually quite high, if you look at the 3 indexes here, you do not have to worry about them. Also, keep in mind that the more indexes you have, the more it costs to support them (inserts, updates, deletes, etc. .).

Answer 3

IIRC, a rule of thumb is that an index can only be used for search queries that use all columns from a point and to the left. For example, an index for columns (a, b, c, d) can be used if you request (a), (a, b), (a, b, c) or (a, b, c, d) but not on (a, c), for example.

This is the result of how indexes are built; the last column is indexed, then for each value of this column an ​​index is created for the next column, etc.

---

Edit: as BQ indicates, the DBMS can scan the full part “ a ” of the index and search in part “ b ” (I did not know that this was really done). However, this is not as fast as an index that can use the rule as described above (OTOH may be faster than a full table scan).

Personally, I do not think that this should be intentionally involved. If perf is enough to bother with a given query that you are considering which indexes are needed, you can also give it the correct ones.