Filter
Top Products
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