Before you create indexes, consider how they might be used. For DSS queries, first evaluate the query performance without any indexes at all. If analysis of the query plan indicates that an index might be useful, follow the guidelines in this section. OLTP transactions, on the other hand, usually benefit from indexes as suggested in this section.
The columns that you choose for an index depend on the data in the columns and the queries that might use the index. Consider how a query or transaction might use the following kinds of indexes:
For information about when to use bitmap indexes, see Bitmap Indexes.
If a column in a large table is often used as a filter in a WHERE clause, consider placing an index on it. The optimizer can use the index to select the required rows and avoid a sequential scan of the entire table. One example is a table that contains a large mailing list. If you find that a postal-code column is often used to filter data, consider creating an index on that column.
This strategy yields a net savings of time only when the cardinality of the column is high; that is, when indexed values are duplicated in only a small fraction of rows. Because nonsequential access through an index takes more disk I/O operations than sequential access, the database server might as well read the table sequentially if a filter expression on the column passes more than a quarter of the rows. Usually, indexing a filter column saves time in the following cases:
When duplicate keys are indexed in a conventional B-tree index, entries that match a given key value are grouped in lists. The database server uses these lists to locate rows that match a requested key value.
If the selectivity of the index column is high, these lists are generally short. But if only a few unique values occur, the lists become long and can even cross multiple leaf pages.
Creating a conventional index on a column that has low selectivity (that is, a small number of distinct values relative to the number of rows) can actually slow performance because of the cost of searching through the duplicate index key values for the rows that satisfy the query.
You can address this problem in both of the following ways:
For information about the CREATE INDEX statement with the USING BITMAP expression, refer to the IBM Informix: Guide to SQL Syntax.
For more information about creating composite indexes, refer to IBM Informix: Guide to SQL Syntax.