Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Primary Keys and Indexes


Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns.

Primary keys are automatically indexed to facilitate effective information retrieval. The primary key index is the most effective access path for the table.

Table columns that are in the primary key, a unique constraint or used in a foreign key reference are automatically indexed (in the order in which they are defined in the key). Therefore, explicitly creating an index on these columns will not improve performance at all.

Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).

An example of when a secondary index may be useful is when a search is regularly performed on a non-keyed column in a table with many rows, defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.

It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated.

Indexes are internal structures which cannot be explicitly accessed by the user once created. An index will be used if the internal query optimization process determines it will improve the efficiency of a search.

SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute each query, which may or may not involve using an applicable index.

WORD_SEARCH Index Algorithm

The WORD_SEARCH index algorithm improves performance for "begins word" searches and "match word" searches, when using the builtin.begins_word() and builtin.match_word() functions.

 create table documents (id integer primary key, title varchar(50),
     content nvarchar(500) collate english_1);
 create index dcont_ws on documents (content for word_search);
 select * from documents where builtin.word_match(content, 'Mimer');

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX