There is no simple formula to determine whether an index should be created. You must consider the trade-off of the benefits of indexed retrieval versus the maintenance overhead of that index.
Consider these factors in determining if you should create an index:
- Keys and unique columns – Unwired WorkSpace automatically creates indexes for findByPrimaryKey object queries. You should not create additional indexes on these columns. The exception is composite keys, which can sometimes be enhanced with additional 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. Also, you should not create an index on a column that has only a few distinct values.
- Order by – if you use object queries (also called dynamic queries) with "order by," then you might require indexes for ordering columns to ensure that the database can use an index for ordering, rather than creating a temporary table which can be slow on a mobile device.