Types of Queries

Sybase recommends certain index types for particular types of queries.

You should know 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, and/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:

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

This table shows recommended index types based on the query. The index that is usually fastest for each query is listed first, the slowest last. These recommendations should not be your only criteria for choosing an index type. You should also consider the number of unique values and disk space. See the other tables in this section.

Query type/index
Type of Query Usage	Recommended Index Type
In a SELECT projection list	Default
In calculation expressions such as SUM(A+B)	Default
As AVG/SUM argument	HNG, LF, HG, Default
As MIN/MAX argument	LF, HG, HNG
As COUNT argument	Default
As COUNT DISTINCT, SELECT DISTINCT or GROUP BY argument	LF, HG, Default
As analytical function argument	LF, Default
If field does not allow duplicates	HG
Columns used in ad hoc join condition	Default, HG, LF,
Columns used in a join index	HG, LF
As LIKE argument in a WHERE clause	Default
As IN argument	HG, LF
In equality or inequality (=, !=)	HG, LF; also CMP
In range predicate in WHERE clause (>, <, >=, <=, BETWEEN	LF, HG, or HNG; also CMP, DATE, TIME, DTTM
In DATEPART equality, range, and IN list predicates	DATE, TIME, DTTM
In a CONTAINS predicate	WD, TEXT

While HNG is recommended, in certain cases LF or HG is faster, and is often used in place of HNG. HNG tends to give consistent performance, while the performance of LF or HG with ranges depends on the size of the range selected.

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 a more important factor.
With the set functions COUNT, COUNT DISTINCT, SUM, MIN, MAX, and AVG, in order to use any index other than the default, the entire query must be resolvable using a single table or join index.
BIT data can only be used in the default index; VARBINARY data greater than 255 bytes can only be used in the default, TEXT, and CMP index types; CHAR and VARCHAR data greater than 255 bytes can only be used in the default, CMP, TEXT, and WD index types; LONG VARCHAR data can only be used 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.