Criteria for Choosing Indexes

The set of indexes that you define for any given column can have dramatic impact on the speed of query processing.

Use the recommendations for all criteria in combination, rather than individually. Remember also that all columns are automatically stored in a way that facilitates fast projections. To decide on additional indexes, look closely at the data in each column. Try to anticipate the number of unique and total values, the query results users want from it, and whether the data is used in ad hoc joins or join indexes.

For details of index types, and criteria to use for choosing the correct types, see the sections that follow.

• Number of Unique Values in the Index
  Sybase IQ indexes are optimized according to the number of unique (distinct) values they include.
• Types of Queries
  Sybase recommends certain index types for particular types of queries.
• Disk Space Usage
  The following table provides estimates of the amount of space each index uses compared to the amount of column data from the source database or flat file.
• Data Types in the Index
  When indexing a column, choose only supported index types for the column data type.
• Combining Index Types
  When a column will be used in more than one type of query, more than one column index type might be appropriate.