Index Types Comparison

Choose the column index type appropriate to your task.

Index Type Comparison
Index Type	Purpose
Compare (CMP) Index	Indexes relationship between any two distinct columns with identical data types, precision, and scale.
Containment (WD) Index	Stores words from a column string of `CHAR`, `VARCHAR`, or `LONG VARCHAR` data.
Date (DATE) and Datetime (DTTM) Indexes	Processes queries involving date or datetime quantities.
Fast Projection (FP) Index	Optimizes projections and enables certain kinds of search conditions to be evaluated. Assigned by default to any permanent table.
High_Group (HG) Index	Used for join columns with integer data types. Handles `GROUP BY` and equality operations efficiently. Recommended for columns with more than 1000 distinct values.
High_Non_Group (HNG) Index	Used for high-cardinality operations involving ranges or aggregates.
Low_Fast (LF) Index	Ideal for columns with under 1000 unique values, such as Yes/No, or number of dependents, or with `SUM`, `AVG`, and `COUNT`. Fastest index type in SAP Sybase IQ.
TEXT Index	Used for unstructured data analytics functionality, an SAP Sybase IQ licensed option.
Time (TIME) Index	Processes queries involving time quantities.

Containment (WD) Index
The Containment (WD) index allows you to store words from a column string of CHAR, VARCHAR, and LONG VARCHAR data.
Compare (CMP) Index
A Compare (CMP) index is the relationship between two columns. You may create Compare indexes on any two distinct columns with identical data types, precision, and scale. The CMP index stores the binary comparison (<, >, or =) of its two columns.
Date (DATE) and Datetime (DTTM) Indexes
DATE and DTTM index types process queries involving date or datetime quantities.
Fast Projection (FP) Index
By default, the database engine automatically creates a Fast Projection (FP ) index on all columns. FP indexes optimize projections and allow the database engine to evaluate certain kinds of search conditions.
High_Group (HG) Index
The High_Group index is commonly used for join columns with integer data types. It is also more commonly used than High_Non_Group because it handles GROUP BY efficiently.
High_Non_Group (HNG) Index
Add an HNG index for range searches.
Low_Fast (LF) Index
This index is ideal for columns that have a very low number of unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, wage class, and so on. LF is the fastest index in SAP Sybase IQ.
TEXT Index
To use TEXT indexes, you must be specifically licensed to use the unstructured data analytics functionality.
Time (TIME) Index
Use the TIME index to process queries involving time quantities.

Parent topic: Index SAP Sybase IQ Columns