Sybase 12.4.2 User Manual
Page 162
Choosing an index type
142
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
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.
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
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
or
GROUP BY
argument.
LF
, Default
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 (>, <,
>=, <=,
BETWEEN
LF
or
HNG