ASA SQL User's Guide
Query Optimization and Execution
Indexes
There is no simple formula to determine whether or not an index should be created for a particular column. You must consider the tradeoff of the benefits of indexed retrieval versus the maintenance overhead of that index. The following factors may help to determine whether you should create an index.
Keys and unique columns Adaptive Server Anywhere automatically creates indexes on primary keys, foreign keys, and unique columns. You should not create additional indexes on these columns. The exception is composite keys, which can sometimes be enhanced with additional indexes.
For more information, see Composite indexes.
Frequency of search If a particular column is searched frequently, you can achieve performance benefits by creating an index on that column. Creating an index on a column that is rarely searched may not be worthwhile.
Size of table Indexes on relatively large tables with many rows provide greater benefits than indexes on relatively small tables. For example, a table with only 20 rows is unlikely to benefit from an index, since a sequential scan would not take any longer than an index lookup.
Number of updates An index is updated every time a row is inserted or deleted from the table and every time an indexed column is updated. An index on a column slows the performance of inserts, updates and deletes. A database that is frequently updated should have fewer indexes than one that is read-only.
Space considerations Indexes take up space within the database. If database size is a primary concern, you should create indexes sparingly.
Data distribution If an index lookup returns too many values, it is more costly than a sequential scan. Adaptive Server Anywhere does not make use of the index when it recognizes this condition. For example, Adaptive Server Anywhere would not make use of an index on a column with only two values, such as employee.sex in the sample database. For this reason, you should not create an index on a column that has only a few distinct values.
The Index Consultant is a tool that assists you in the selection of an appropriate set of indexes for your database. For more information, see Index Consultant overview.
You can create indexes on both local and global temporary tables. You may want to consider indexing a temporary table if you expect it will be large and accessed several times in sorted order or in a join. Otherwise, any improvement in performance for queries is likely to be outweighed by the cost of creating and dropping the index.
For more information, see Working with indexes.