# SQL Create Index

> An SQL index, which corresponds to an underlying eXtremeDB index, can be created to improve lookup performance for specific queries. (Please see SQL Optimizer for details about performance optimization using indexes.) Indexes are created using the SQL `create index` statement specifying the table and key field(s) For example:

>         create table t (x integer);
>         create index tx on t(x);

> Here a B-Tree (tree) index (the default type) is created on column (field) `x` of table `t`.

> The syntax is as follows:

>         CREATE [UNIQUE] INDEX name ON table "(" key { "," key } ")"
>             [USING (HASH | RTREE | PTREE | TRIGRAM | INCLUSIVE)]
>             [IF NOT EXISTS]

>         key : column_name [ asc | desc ]

> Note that all SQL keywords are case insensitive in eXtremeSQL - i.e. `CREATE INDEX` and `create index` are equivalent.

> The keywords `HASH, RTREE, PTREE,` and `TRIGRAM` refer to the eXtremeDB indexes of type ``hash`` (hash table), `rtree` (spatial search), ``trie`` (Patricia Trie), `trigram` (Trigram search) respectively. The keyword `INCLUSIVE` indicates that the index is a key-value-inclusive index.

> A `key` specification can include the keywords `ASC` or `DESC` to indicate whether the index sorts in ascending or descending order. And note that compound indexes can be created by specifying a comma-delimited list of keys.

> ### If Not Exists

> The `if not exists` clause can be used to override an error when the index already exists. For example, note how the following c`reate index` statement fails while adding the `if not exists` clause allows the execution to succeed:

>         XSQL>create table foo(x integer);
>         XSQL>insert into foo values (1);
>         XSQL>create index idx on foo(x);
>         XSQL>create index idx on foo(x);
>         ERROR: Compiler error at position 24: Index idx already defined for table foo
>         create index idx on foo(x)
>         ^
>         XSQL>create index if not exists idx on foo(x);
>         XSQL>select * from foo;
>         x
>         ------------------------------------------------------------------------------
>         1

>         Selected records: 1