SQL - CREATE INDEX

Creates a new index. Indexes can be

  • Unique Where they don’t allow duplicates.
  • Not Unique Where they allow duplicates.
  • Full Text Where they index any single word of text.

There are several index algorithms available to determine how OrientDB indexes your database. For more information on these, see Indexes.

Syntax

  1. CREATE INDEX <name> [ON <class> (<property>)] <index-type> [<key-type>]
  2.              METADATA [{<json>}]
  • <name> Defines the logical name for the index. If a schema already exists, you can use <class>.<property> to create automatic indexes bound to the schema property. Because of this, you cannot use the period “.“ character in index names.
  • <class> Defines the class to create an automatic index for. The class must already exist.

  • <property> Defines the property you want to automatically index. The property must already exist.

    If the property is one of the Map types, such as LINKMAP or EMBEDDEDMAP, you can specify the keys or values to use in index generation, using the BY KEY or BY VALUE clause.

  • <index-type> Defines the index type you want to use. For a complete list, see Indexes.

  • <key-type> Defines the key type. With automatic indexes, the key type is automatically selected when the database reads the target schema property. For manual indexes, when not specified, it selects the key at run-time during the first insertion by reading the type of the class. In creating composite indexes, it uses a comma-separated list of types.
  • METADATA Defines additional metadata through JSON.

To create an automatic index bound to the schema property, use the ON clause, or use a <class>.<property> name for the index. In order to create an index, the schema must already exist in your database.

In the event that the ON and <key-type> clauses both exist, the database validates the specified property types. If the property types don’t equal those specified in the key type list, it throws an exception.

You can use list key types when creating manual composite indexes, but bear in mind that such indexes are not yet fully supported.

Examples

  • Create a manual index to store dates:

    1. orientdb> CREATE INDEX mostRecentRecords UNIQUE DATE

  • Create an automatic index bound to the new property id in the class User:

    1. orientdb> CREATE PROPERTY User.id BINARY
    2. orientdb> CREATE INDEX User.id UNIQUE

  • Create a series automatic indexes for the thumbs property in the class Movie:

    1. orientdb> CREATE INDEX thumbsAuthor ON Movie (thumbs) UNIQUE
    2. orientdb> CREATE INDEX thumbsAuthor ON Movie (thumbs BY KEY) UNIQUE
    3. orientdb> CREATE INDEX thumbsValue ON Movie (thumbs BY VALUE) UNIQUE

  • Create a series of properties and on them create a composite index:

    1. orientdb> CREATE PROPERTY Book.author STRING
    2. orientdb> CREATE PROPERTY Book.title STRING
    3. orientdb> CREATE PROPERTY Book.publicationYears EMBEDDEDLIST INTEGER
    4. orientdb> CREATE INDEX books ON Book (author, title, publicationYears) UNIQUE

  • Create an index on an edge’s date range:

    1. orientdb> CREATE CLASS File EXTENDS V
    2. orientdb> CREATE CLASS Has EXTENDS E
    3. orientdb> CREATE PROPERTY Has.started DATETIME
    4. orientdb> CREATE PROPERTY Has.ended DATETIME
    5. orientdb> CREATE INDEX Has.started_ended ON Has (started, ended) NOTUNIQUE

    You can create indexes on edge classes only if they contain the begin and end date range of validity. This is use case is very common with historical graphs, such as the example above.

  • Using the above index, retrieve all the edges that existed in the year 2014:

    1. orientdb> SELECT FROM Has WHERE started >= '2014-01-01 00:00:00.000' AND 
    2.           ended < '2015-01-01 00:00:00.000'

  • Using the above index, retrieve all edges that existed in 2014 and write them to the parent file:

    1. orientdb> SELECT outV() FROM Has WHERE started >= '2014-01-01 00:00:00.000' 
    2.           AND ended < '2015-01-01 00:00:00.000'

  • Using the above index, retrieve all the 2014 edges and connect them to children files:

    1. orientdb> SELECT inV() FROM Has WHERE started >= '2014-01-01 00:00:00.000' 
    2.           AND ended < '2015-01-01 00:00:00.000'

  • Create an index that includes null values.

    By default, indexes ignore null values. Queries against null values that use an index returns no entries. To index null values, see { ignoreNullValues: false } as metadata.

    1. orientdb> CREATE INDEX addresses ON Employee (address) NOTUNIQUE
    2.            METADATA { ignoreNullValues : false }

For more information, see