Types of queries

You should know in advance how data in the columns will generally be queried. For example:

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

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

Table 6-3: 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

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: