[Informix] Indexing best practices -- Posted by inturi on Saturday, June 27 2009
Create an index on:
Join columns (used in WHERE clause for multi-table joins)
Highly-selective filter columns (columns used in WHERE clauses)
Columns frequently used for ordering and grouping (sorting, columns used in ORDER BY and GROUP BY clauses)
Also columns used in UNIQUE / DISTINCT clauses
And columns used in UNION statements (where we combine queries)

Avoid highly duplicate indexes
Prefer indexes on high-selectivity columns
You can use composite indexes to increase index selectivity
Limit index in highly-updated table/columns and volatile/temp tables
Remember overhead cost of index maintenance (btree cleaner)
Limit number of indexes on tables, use only the needed
Keep key size small
Long character strings are not good candidates for indexes
Try to use small columns, like smallint, integer, date, char()
Keep the number of indexes small
Create only the indexes you really need

Index only if you need to access <= 4 or 5 % of the data in a table
The alternative to using an index to access row data in a table is to read the entire table sequentially from top to bottom (sequential scan or full table scan)
Sequential scans are better for queries that require a high percentage of the data in a table

Remember: Using indexes to retrieve rows requires two reads: an index read followed by a table/data read
Avoid indexes on relatively small tables
Sequential table scans are just fine for small tables
There is no need to store both table and index data for small tables
Create primary keys (or, even better, explicit unique index followed by a primary key constraint) for all tables, as possible
Remember: Even if you don’t explicitly create an index for a primary, foreign key and unique constraint, Informix will implicitly create an index for you

Use composite indexes to increase uniqueness
Composite indexes may need to be used where single-column values may not be unique by themsel ves
Remember: In composite indexes, the driving is the first column, and this should be the most selective column in the index
Use clustered indexes to speed up retrieval
Remember: Only a single cluster index is allowed per table, and will reorder the data rows according to the index. See guidelines for cluster

Disable indexes before large DML operations
Massive loads, inserts, deletes, updates
You can re-enable the indexes and constraints after the large DML operation is finished

Remember:
The index on a table should be based on the types of queries you expect to occur against the table's columns
Always give priority to the most frequently executed queries
More indexes than the ones you need will produce the cost-based query Optimizer to do additional work to decide which index to use

Unless all table indexes receive the same type of activity it is recommended to use the FILLFACTOR option in the CREATE INDEX statement. Ex:

CREATE INDEX state_code_idx ON state(code)
FILLFACTOR 80;

A high fill factor will produce an initially compact/dense index, providing more efficient caching and reducing the number of pages to read when retrieving rows
Use a FILLFACTOR of 100 for tables that are receive selects (read only) or deletes to minimize the merging and shuffling pages as keys are removed

Creating an index with a lower fill factor will produce a sparse index (with more pages and probably levels to read), which can delay the need for node (page) splitting and the accompanying performance impact