When and Where to use Indexes

Indexes are the primary tuning mechanisms inside SAP Sybase IQ. Knowing when and where to use indexes can make your queries run faster.

Always use indexes on:

• Join columns (HG index regardless of cardinality)
• Searchable columns (HG or LF index based on cardinality)

• DATE, TIME, and DATETIME/TIMESTAMP columns (DATE, TIME, DTTM)

  The DATE, TIME, or DATETIME/TIMESTAMP column should also have an LF or HG index depending on data cardinality.
• If you are uncertain whether the column will be used heavily, place an LF or HG index on the column. Workload Management can subsequently be enabled to monitor the use of indexes.
• Use PRIMARY KEY, UNIQUE CONSTRAINT, or UNIQUE HG indexes where appropriate, as they provide SAP Sybase IQ with additional information about the unique data in the indexed column(s).
• A column with an HNG or CMP index should have a corresponding LF or HG index
• Indexes are not needed on columns whose data is ONLY returned to the client (projected)