CREATE INDEX

Documentation

Home » Documentation » Using VoltDB

CREATE INDEX

CREATE INDEX — Creates an index for faster access to a table.

Synopsis

CREATE [UNIQUE|ASSUMEUNIQUE] INDEX index-name ON table-name ( index-column [,...])

Description

Creating an index on a table makes read access to the table faster when using the columns of the index as a key. Note that VoltDB creates an index automatically when you specify a constraint, such as a primary key, in the CREATE TABLE statement.

When you specify that the index is UNIQUE, VoltDB constrains the table to at most one row for each set of index column values. If an INSERT or UPDATE statement attempts to create a row where all the index column values match an existing indexed row, the statement fails.

Because the uniqueness constraint is enforced separately within each partition, only indexes on replicated tables or containing the partitioning column of partitioned tables can ensure global uniqueness for partitioned tables and therefore support the UNIQUE keyword.

If you wish to create an index on a partitioned table that acts like a unique index but does not include the partitioning column, use the keyword ASSUMEUNIQUE instead of UNIQUE. Assumed unique indexes are treated like unique indexes (VoltDB verifies they are unique within the current partition). However, it is your responsibility to ensure these indexes are actually globally unique. Otherwise, it is possible an index will generate a constraint violation during an operation that modifies the partitioning of the database (such as adding nodes on the fly or restoring a snapshot to a different cluster configuration).

The indexed items (index-column) are either columns of the specified table or expressions, including functions, based on the table. For example, the following statements index a table based on the calculated area and its distance from a set location:

CREATE INDEX areaofplot ON plot (width * height);
CREATE INDEX distancefrom49 ON plot ( ABS(latitude - 49) );

By default, VoltDB creates a tree index. Tree indexes provide the best general performance for a wide range of operations, including exact value matches and queries involving a range of values, such as SELECT ... WHERE Score > 1 AND Score < 10.

If an index is used exclusively for exact matches (such as SELECT ... WHERE MyHashColumn = 123), it is possible to create a hash index instead. To create a hash index, include the string "hash" as part of the index name.

Examples

The following example creates two indexes on a single table. The first is, by default, a non-unique index based on the departure time The second is a unique index based on the columns for the airline and flight number.

CREATE INDEX flightTimeIdx ON FLIGHT ( departtime );
CREATE UNIQUE INDEX FlightKeyIdx ON FLIGHT ( airline, flightID );

You can also use functions in the index definition. For example, the following is an index based on the element movie within a JSON-encoded VARCHAR column named favorites and the member's ID.

CREATE INDEX FavoriteMovie ON MEMBER ( 
       FIELD( favorites, 'movie' ), memberID
);