In SQL, in what situation do we want to Index a field in a table, or 2 fields in a table at the same time?

Question

In SQL, it is obvious that whenever we want to do a search on millions of record, say CustomerID in a Transactios table, then we want to add an index for CustomerID.

Is another situation we want to add an index to a field when we need to do inner join or outer join using that field as a criteria? Such as Inner join on t1.custumerID = t2.customerID. Then if we don't have an index on customerID on both tables, we are looking at O(n^2) because we need to loop through the 2 tables sequentially. If we have index on customerID on both tables, then it becomes O( (log n) ^ 2 ) and it is much faster.

Any other situation where we want to add an index to a field in a table?

What about adding index for 2 fields combined in a table. That is, one index, for 2 fields together?

Answer 1

Often used fields on the ORDER BY and WHERE clauses are also good candidates for indexing.

Answer 2

You may want to consider an index on two fields if you are either always selecting on two fields or selecting on one field and ordering on another.

You may also want to include a second field into a composite index if the cardinality of the first column is so small that it may result in skewed indexes. This is often the case with a status field that has a limited (say 3 or 4) possible values so including a field with a wide variety of values e.g. a datetime field that you generally sort on will produce a more performant index.