Indexes (IQ)

Before creating IQ indexes, you should consider the implications of various types of indexes on the database server memory and disk space. The set of indexes you define for any given column can have dramatic impact on the speed of query processing.

There are four main criteria for choosing indexes:

Number of unique values
Types of queries
Disk space usage
Data types

You should consider all criteria in combination, rather than individually. Try to anticipate for the data in each column, the number of unique and total values, the query results users will want, and whether the data will be used in ad hoc joins or join indexes.

The following types of index are available:

HG – HighGroup indexes are used for GROUP BY, COUNT(DISTINCT) and SELECT DISTINCT statements when data has more than 1000 unique values
HNG – HighNonGroup indexes make equality comparisons, SUM and AVG calculations very fast when data has more than 1000 unique values. Nonequality comparisons can also be done
LF – LowFast indexes are used for columns that have a very low number of unique values. This index also facilitates join index processing (Join Indexes (IQ/Oracle)). It is one of the two indexes allowed for columns used in join relationships.
CMP – Compare indexes are used for columns that store the binary comparison (<, >, or =) of any two distinct columns with identical data types, precision, and scale.
TEXT – Full text indexes (see Full Text Searches (IQ/SQL Anywhere)).
WD – Used to index keywords by treating the contents of a CHAR or VARCHAR column as a delimited list.
DATE, TIME, and DTTM – For date and timestamp columns.

For detailed information about choosing index types, see your IQ documentation.