The XDB Server uses B-tree indexes to speed retrieval. Like a book index, a database index reduces the amount of data that must be searched to find an answer. The XDB Server performs automatic query optimization to select the best index to use for a query.
In general, the fewer records accessed, the faster the retrieval time. The XDB Server uses available indexes whenever they help to speed retrieval. But, while indexes generally speed up retrieval, they can slow down updates, inserts and deletes.
Usually, it is faster to execute a query using one or more indexes than to examine every record in the tables involved in the query. The XDB query optimization algorithms examine the indexes available and pick which indexes, if any, to use for a particular query. The user does not need to specify which indexes to use.
The query optimization algorithms analyze the selectivity of a column to determine how to process a query. Selectivity of a column is determined by calculating how unique the values in the column are. The more unique the values, the more likely the column will be a candidate for index use. Selectivity is determined by the formula, 1/x, where x is the number of distinct values in a column or index. The larger the number of distinct values, the more unique the column is and the lower the selectivity is. This increases the probability that the optimizer will use the index.
If a command does not contain a WHERE, ORDER BY, HAVING, or GROUP BY clause, the server generally will not perform an index search; instead, it processes the table sequentially. Some aggregate functions can cause an index to be used in certain cases. Usually, if a column in the WHERE clause is indexed, the index is a candidate for use. Other factors, such as the number of distinct values in an index, determine final index selection.