You can use indexes to improve the performance of queries. A query that references an indexed column in its WHERE clause can use the index. If the query selects only the indexed column, the query can read the indexed column value directly from the index, rather than from the table.
If all the fields in the SELECT list of a query are in an index, then the solidDB® optimizer can simply use that index, rather than doing an extra lookup to read the complete record. Similarly, if all the fields of a WHERE clause are in an index, then the optimizer can use that index — if the information in the index is enough to prove that the record will not qualify for the WHERE clause, then the optimizer can avoid looking up the complete record.
For example, suppose that we have a WHERE clause that refers to two or morecolumns, for example:
WHERE col1 = x AND col2 >= a AND col2 <=b
Suppose further that we have an index that contains both col1 and col2, and that has either col1 or col2 as the leading column of the key. For example, if we have an index on col2 + col3 + col1 then this index contains both columns, and one of those columns (col2) is the leading column in the key. If the user’s query is
SELECT col1, col4 FROM table1 WHERE col1 = x AND col2 >= a AND col2 <=b;
then we do not need to look up the complete record unless the search criteria are met. After all, if the search criteria are not met, then we do not care what value col4 has and so we do not need to look up the full record.
If a table has a primary key, solidDB® orders the rows on disk in the order of the values of the primary key. Since the rows are physically in order by the primary key, the primary key itself serves as an index, and optimization tips that apply to indexes also apply to the primary key.
If the table does not have a user-specified primary key, then the rows are ordered using the ROWID. The ROWID is assigned to each row when it is inserted, and each record gets a larger ROWID than the record inserted before it. Thus, in tables without user-specified primary keys, the records are stored in the order in which those rows were inserted. For more information about primary keys, read Primary key indexes.
Searches with row value constructor constraints are optimized to use an index if an index is available. For efficiency, solidDB® uses an index to resolve row value constructor constraints of the form (A, B, C) >= (1, 2, 3), where the operator may be any of the following: <, <=,>= and >. (The server does not use an index to resolve row value constructor constraints that contain the operators =, !=, or <>. The server may use an index to resolve other types of constraints that use =, !=, or <>.) For more information about row value constructors, see Row value constructors.
Indexes improve the performance of queries that select a small percentage of rows from a table. You should consider using indexes for queries that select less than 15% of table rows.