QL Create Index

SQL Create Index

Creates a new index. To create an automatic index bound to a schema property use section "ON" of create index command or use as name the <class.property> notation. But assure to have created the schema for it before the index. See the example below.

Indexes can be:

• UNIQUE, doesn't allow duplicated
• NOTUNIQUE, allows duplicates
• FULLTEXT, by indexing any single word of the text. It's used in query with the operator CONTAINSTEXT

Syntax

    CREATE INDEX <name> [ON <class-name> (prop-names)] <type> [<key-type>]

Where:

• name logical name of index. Can be . to create an automatic index bound to a schema property. In this case class is the class of the schema and property, is the property created into the class. Notice that in another case index name can't contain '.' symbol
• class-name name of class that automatic index created for. Class with such name must already exist in database
• prop-names comma-separated list of properties that this automatic index is created for. Property with such name must already exist in schema. If property belongs to one of the Map types (LINKMAP, EMBEDDEDMAP) you can specify will be keys or values used for index generation. Use "by key" or "by value" expressions for that, if nothing will be specified keys will be used during index creation.
• type, between 'unique', 'notunique' and 'fulltext'
• key-type, is the type of key (Optional). On automatic indexes is auto-determined by reading the target schema property where the index is created. If not specified for manual indexes, at run-time during the first insertion the type will be auto determined by reading the type of the class. In case of creation composite index it is a comma separated list of types.

If "ON" and key-type sections both exist database validate types of specified properties. And if types of properties not equals to types specified in key-type list, exception will be thrown.

List of key types can be used for creation manual composite indexes, but such indexes don't have fully support yet.

Examples

Examples of non-automatic index to store dates manually:

    CREATE INDEX mostRecentRecords unique date

Examples of automatic index bound to the property "id" of class "User":

    CREATE PROPERTY User.id BINARY
    CREATE INDEX User.id UNIQUE

Examples of index for "thumbs" property of class "Movie".

    CREATE INDEX thumbsAuthor ON Movie (thumbs) unique;
    CREATE INDEX thumbsAuthor ON Movie (thumbs by key) unique; 
    CREATE INDEX thumbsValue ON Movie (thumbs by value) unique; 

Composite index example:

    CREATE PROPERTY Book.author STRING
    CREATE PROPERTY Book.title STRING
    CREATE PROPERTY Book.publicationYears EMBEDDEDLIST INTEGER
    CREATE INDEX books ON Book (author, title, publicationYears) UNIQUE

For complete index guide look at Index guide.

To know more about other SQL commands look at SQL commands.