Sybase 12.4.2 manual 142, Query type/index, Type of Query Usage Recommended Index Type

Choosing an index type

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 Adaptive Server 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 picking an index type. You should also consider the number of unique values and disk space. See the other tables in this section.

Table 4-2: Query type/index
Type of Query Usage	Recommended Index Type
In a SELECT projection list	Default
In calculation expressions such as	Default
SUM(A+B)
As AVG/SUM argument	High_Non_Group, Low_Fast,
	High_Group, Default
As MIN/MAX argument	LF, HG, HNG
As COUNT argument	LF, HG
As COUNT DISTINCT, SELECT DISTINCT	LF, Default
or GROUP BY argument.
If field does not allow duplicates	HG
Columns used in ad hoc join	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
In range predicate in WHERE clause (>, <,	LF or HNG
>=, <=, BETWEEN

Note 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.

142