Types of Queries

Certain index types optimize particular types of queries.

Determine in advance how data in the columns will generally be queried. For example:

Will the column be part of a join predicate?
If the column has a high number of unique values, will the column be used in a GROUP BY clause, be the argument of a COUNT DISTINCT, or be in the SELECT DISTINCT projection?
Will the column frequently be compared with another column of the same data type, precision, and scale?

Often, the type of data in a column gives a good indication how the column will be used. For example, a date column will probably be used for range searches in WHERE clauses, and a column that contains prices or sales amounts will probably be used in the projection as an argument for aggregate functions (SUM, AVG, and so on).

Note: SAP Sybase IQ can still resolve queries involving a column indexed with the wrong index type, although it may not do so as efficiently.

For optimal query performance, columns used in join predicates, subquery predicates, GROUP BY and DISTINCT clauses should have either a HG or LF index, since IQ has no statistics other than the index for the optimizer to use. Use HG for high cardinality and LF for low cardinality columns, except for tables with fewer than 100,000 rows which should have HG.

These estimates are generally valid; however, other factors can take precedence:

For range predicates, the number of unique values is the most important factor.
When using the set functions COUNT, COUNT DISTINCT, SUM, MIN, MAX, and AVG, to use any index other than the default, the entire query must be resolvable using a single table.
BIT data can be used only in the default index; VARBINARY data greater than 255 bytes can be used only in the default, TEXT, and CMP index types; CHAR and VARCHAR data greater than 255 bytes can be used only in the default, CMP, TEXT, and WD index types; LONG VARCHAR data can be used only in the default, TEXT, and WD index types; only DATE data can be used in the DATE index type; only TIME data can be used in the TIME index type; only DATETIME and TIMESTAMP data can be used in the DTTM index type.