Answers to some simple questions can help you choose the right index for a column.
Is the cardinality greater than 1500-2000?
If the answer is yes, place an HG index on this column. If not, place an LF index on the column.
Does the column contain DATE, TIME, DATETIME, or TIMESTAMP data?
If the answer is yes, place a DATE, TIME, or DTTM index on this column. You should also place an LF or HG on the column.
Will the column be used in range searches or aggregations?
If the answer is yes, place an HNG index on the column. You should also place an LF or HG should be on the column. If the aggregation contains more than just the column, an HNG may not be appropriate. In most cases an HNG index is not needed as the LF or HG indexes have more than enough capability to perform the aggregations. This does not apply to DATE, TIME, or DATETIME types.
Will this column be used for word searching?
If the answer is yes, place a WD index on the column. An LF or HG index is not necessary and would consume significant space.
Will this column be used for full text searching?
If the answer is yes, place a TEXT index on the column. An LF or HG is not necessary and would consume significant space.
Will two columns in the same table be compared to each other (A = B, A < B, A > B, A <= B, A > = B)?
If the answer is yes, place a CMP index on the two columns.
Will this column, or set of columns, be used in GROUP BY or ORDER BY statements?
If the answer is yes, place an HG index on the column, or columns in the GROUP BY or ORDER BY statement. Each column should also have a corresponding HG or LF index.
Is this column part of a multicolumn primary key, constraint, or index?
If the answer is yes, place an HG or LF index on each column in the multicolumn index.