Create Index

An index helps speed up queries with some type of condition such as WHERE clause. Indexes can be created or dropped with no effect on the data. Indexes can also be unique, similar to the UNIQUE constraint. A Unique index is not only used for performance, but also for data integrity.

Since indexes are special lookup tables in library like other database systems, their primary usage is to speed up data retrieval, however they can also slow UPDATE and INSERT statements due to the overhead of maintaining these special tables. An index in a database is very similar to an index in a book. Indexes are automatically created for primary key constraints and unique constraints. These indexes starts with sys_autoindex_ prefix and can be viewed in sys_objects table.

SQLDatabase.Net library uses B plus tree to create indexes, it does not support other type of indexes as of version 2.0.1.0, you can read more about it on Wikipedia https://en.wikipedia.org/wiki/B%2B_tree to get better understanding of how database indexing works.

The Create Index statement is used to create index, based on either one or more columns. Each index statement must have a name, a table and column(s) of those tables which needs to be in that particular index.

CREATE [UNIQUE] INDEX [IF NOT EXISTS] [SchemaName].IndexName ON TableName ( Columns separated by comma with direction of [ASC] or [DESC] )

A unique index only permit unique values in columns, if there are multiple schema attached to database connection then you should provide the schema name. Partial indexes are not supportted, for library to use index the query must use all columns but they do not have to be in correct order. Indexes are only supported on tables not on views. A table can have multiple indexes and a column can be part of more than one index.

DROP TABLE IF EXISTS StudentScores;
CREATE TABLE StudentScores
(
StudentId  Integer primary key
, TestName Text
, Score Real
);
INSERT INTO StudentScores values (1,'Math', 88);
INSERT INTO StudentScores values (2,'Math', 75);
INSERT INTO StudentScores values (3,'Math', 49);
INSERT INTO StudentScores values (4,'Math', 65);
INSERT INTO StudentScores values (5,'Math', 42);
INSERT INTO StudentScores values (6,'Math', 55);

Below are three different examples on how to create an index, when creating an index review the queries which will be executed against that table and their where clauses. Indexes are useful to speed up search only If your query contains conditions, such as where clause and join statement ON columns.

CREATE INDEX IdxScore ON StudentScores (Score);
CREATE INDEX IdxTestScore ON StudentScores (TestName, Score);
CREATE UNIQUE INDEX IdxStudentTest ON StudentScores (StudentId, TestName);

To view all the indexes on particular table use the system command, SYSCMD Index_List with table name.

SYSCMD Index_List('StudentScores'); /* StudentScores is table name */

To view information about individual index use the SYSCMD Index_Info command with index name

SYSCMD index_info('IdxTestScore'); /* IdxTestScore is index name */

Drop Index

An index can be dropped using drop index statement, the IF EXISTS is optional and protects against error if index does not exists.

DROP INDEX IF EXISTS IdxTestScore; /* IdxTestScore is index name */

INDEXED BY

A query can be forced to use particular index using Indexed By clause in a statement. If you specify an index which does not contain the where clause columns an error will be generated "cannot use index: [Index Name]".

SELECT * FROM StudentScores INDEXED BY IdxScore WHERE Score = 88;

NOT INDEXED

A query can be forced not to use any index by using NOT INDEXED, the above query can be written as following to skip any index seek.

SELECT * FROM StudentScores NOT INDEXED WHERE Score = 88;

REINDEX

Indexes can be out of order due to large updates, delete or inserts, to keep the index and database integrity on par, a reindex statement is used which can rebuilt index from scratch, the reindex can also increase performance for large indexes by rearranging the data pages. ReIndex can be done on all indexes, particular table or simply the index you want to rebuilt.

ReIndex ; /* All indexes on all tables */
ReIndex 'StudentScores' ; /* Rebuild index on StudentScores table. */
ReIndex 'IdxScore' ; /* Rebuild only IdxScore index. */

Index Avoidance

Although indexes are created to enhance the performance of database, there are times when they should be avoided such as on tables with less rows, columns which contain close to half number of values as NULL, also columns which are frequently changing and number of rows in table are relatively small then index will create overhead slowing queries. Index should also be avoided on tables with large batch inserts or updates with less frequent select statements. During large batch inserts index can be dropped and recreated after the batch is completed.

Tables which contain many rows such as thousands of millions of rows should have indexes, the higher the cardinality the more perfect candidate for index. For an index, cardinality is considered the number of unique values in the index. Following this formula a unique index would have cardinality equal to the number of rows in the table, making such tables and columns perfect candidate for an index.