When and How to Index

There are two basic mechanisms for accessing Texis tables: a table space scan which is sequential, and an index based scan which is direct. Index based retrieval is usually more efficient than table space scan with some exceptions.

The use of indexes is one of the major ways in which the performance of queries (i.e. the speed with which results are retrieved) can be improved in relational databases. Indexes allow the DBMS to retrieve rows from a table without scanning the entire table, much as a library user can use a card catalog to find books without scanning the entire library.

The creation of indexes improves the performance associated with processing large tables. However, an excessive number of indexes can result in an increase in processing time during update operations because of the additional effort needed to maintain the indexes. Thus, for tables undergoing frequent change, there could be a "cost" associated with an excessive number of indexes.

In addition, as the number of indexes increases, the storage requirements needed to hold the indexes becomes significant. Other cautions include not indexing small amounts of data as doing so may slow down searches due to the overhead of looking in an index. The point at which it makes sense to use an index will depend upon the system in use. And it would be important to use the correct kind of index for the job.

Since there are so many factors involved in the decision as to whether and what kind of index will most optimize the search, Texis largely takes over the management of these decisions. The user can suggest those tables on which an index ought to be created. Beyond this, the user would not know the status of the index, which is always in flux, nor whether it has been updated at the time of the search.

Unlike other systems, Texis ensures that all information which has been added to any table can be searched immediately, regardless of whether it has been indexed, and regardless of whether it has been suggested that an index be maintained on that table or not. Sequential table space scans and index based scans are efficiently managed by Texis so that the database can always be searched in the most optimized manner, with the most current information available to the user.

To this end, there are two types of indexes supported by Texis:

The canonical sorted order alphabetical index found in traditional SQL systems, and
The Metamorph index, optimized for systems containing a large number of rows, or a lot of text, or both. The Metamorph index is used when it is expected that LIKE or its variants will be common on the field being indexed.

When an index is created, neither an end user nor an application programmer need (nor can) reference the index in a query. Indexes are used automatically by Texis when needed to choose the best path to the data.