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 you have a WHERE clause that refers to two or more columns, for example:
WHERE col1 = x AND col2 >= a AND col2 <=b
You also 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 you 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 you have the following user query:
SELECT col1, col4 FROM table1 WHERE col1 = x AND col2 >= a AND col2 <=b;
then retrieving the complete record is not required unless the search criteria are met.
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 greater ROWID value 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 can be <, <=, >=, or >. (The server does not use an index to resolve row value constructor constraints that contain the operators =, !=, or <>. The server can 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.