Top.Mail.Ru
space_object:create_index() | Tarantool
Tarantool
Check out the new release 2.8
Submodule box.space space_object:create_index()

space_object:create_index()

object space_object
space_object:create_index(index-name[, options])

Create an index. It is mandatory to create an index for a space before trying to insert tuples into it, or select tuples from it. The first created index, which will be used as the primary-key index, must be unique.

Parameters:
Return:

index object

Rtype:

index_object

Options for space_object:create_index()

Name Effect Type Default
type type of index string (‘HASH’ or ‘TREE’ or ‘BITSET’ or ‘RTREE’) Note re storage engine: vinyl only supports ‘TREE’ ‘TREE’
id unique identifier number last index’s id, +1
unique index is unique boolean true
if_not_exists no error if duplicate name boolean false
parts field-numbers + types {field_no, ‘unsigned’ or ‘string’ or ‘integer’ or ‘number’ or ‘boolean’ or ‘array’ or ‘scalar’, and optional collation, and optional is_nullable value} {1, 'unsigned'}
dimension affects RTREE only number 2
distance affects RTREE only string (‘euclid’ or ‘manhattan’) ‘euclid’
bloom_fpr affects vinyl only number vinyl_bloom_fpr
page_size affects vinyl only number vinyl_page_size
range_size affects vinyl only number vinyl_range_size
run_count_per_level affects vinyl only number vinyl_run_count_per_level
run_size_ratio affects vinyl only number vinyl_run_size_ratio
sequence see section regarding specifying a sequence in create_index() string or number not present

The options in the above chart are also applicable for index_object:alter().

Note re storage engine: vinyl has extra options which by default are based on configuration parameters vinyl_bloom_fpr, vinyl_page_size, vinyl_range_size, vinyl_run_count_per_level, and vinyl_run_size_ratio – see the description of those parameters. The current values can be seen by selecting from box.space._index.

Possible errors:

  • too many parts;
  • index ‘…’ already exists;
  • primary key must be unique.
tarantool> s = box.space.tester
---
...
tarantool> s:create_index('primary', {unique = true, parts = { {field = 1, type = 'unsigned'}, {field = 2, type = 'string'}} })
---
...

Details about index field types:

The seven index field types (unsigned | string | integer | number | boolean | array | scalar) differ depending on what values are allowed, and what index types are allowed.

  • unsigned: unsigned integers between 0 and 18446744073709551615, about 18 quintillion. May also be called ‘uint’ or ‘num’, but ‘num’ is deprecated. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
  • string: any set of octets, up to the maximum length. May also be called ‘str’. Legal in memtx TREE or HASH or BITSET indexes, and in vinyl TREE indexes. A string may have a collation.
  • integer: integers between -9223372036854775808 and 18446744073709551615. May also be called ‘int’. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
  • number: integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, or double-precision floating point numbers. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
  • boolean: true or false. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.
  • array: array of numbers. Legal in memtx RTREE indexes.
  • scalar: booleans (true or false), or integers between -9223372036854775808 and 18446744073709551615, or single-precision floating point numbers, or double-precison floating-point numbers, or strings. When there is a mix of types, the key order is: booleans, then numbers, then strings. Legal in memtx TREE or HASH indexes, and in vinyl TREE indexes.

Additionally, nil is allowed with any index field type if is_nullable=true is specified.

Index field types to use in space_object:create_index()

Index field type What can be in it Where is it legal Examples
unsigned integers between 0 and 18446744073709551615 memtx TREE or HASH indexes,
vinyl TREE indexes
123456
string strings – any set of octets memtx TREE or HASH indexes
vinyl TREE indexes
‘A B C’
‘\65 \66 \67’
integer integers between -9223372036854775808 and 18446744073709551615 memtx TREE or HASH indexes,
vinyl TREE indexes
-2^63
number integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, double-precision floating point numbers memtx TREE or HASH indexes,
vinyl TREE indexes
1.234
-44
1.447e+44
boolean true or false memtx TREE or HASH indexes,
vinyl TREE indexes
false
true
array array of integers between -9223372036854775808 and 9223372036854775807 memtx RTREE indexes {10, 11}
{3, 5, 9, 10}
scalar booleans (true or false), integers between -9223372036854775808 and 18446744073709551615, single-precision floating point numbers, double-precision floating point numbers, strings memtx TREE or HASH indexes,
vinyl TREE indexes
true
-1
1.234
‘’
‘ру’

Allowing null for an indexed key: If the index type is TREE, and the index is not the primary index, then the parts={...} clause may include is_nullable=true or is_nullable=false (the default). If is_nullable is true, then it is legal to insert nil or an equivalent such as msgpack.NULL (or it is legal to insert nothing at all for trailing nullable fields). Within indexes, such “null values” are always treated as equal to other null values, and are always treated as less than non-null values. Nulls may appear multiple times even in a unique index. Example:

box.space.tester:create_index('I',{unique=true,parts={{2,'number',is_nullable=true}}})

Warning

It is legal to create multiple indexes for the same field with different is_nullable values, or to call space_object:format() with a different is_nullable value from what is used for an index. When there is a contradiction, the rule is: null is illegal unless is_nullable=true for every index and for the space format.

Using field names instead of field numbers: create_index() can use field names and/or field types described by the optional space_object:format() clause. In the following example, we show format() for a space that has two columns named ‘x’ and ‘y’, and then we show five variations of the parts={} clause of create_index(), first for the ‘x’ column, second for both the ‘x’ and ‘y’ columns. The variations include omitting the type, using numbers, and adding extra braces.

box.space.tester:format({{name='x', type='scalar'}, {name='y', type='integer'}})
box.space.tester:create_index('I2',{parts={{'x','scalar'}}})
box.space.tester:create_index('I3',{parts={{'x','scalar'},{'y','integer'}}})
box.space.tester:create_index('I4',{parts={1,'scalar'}})
box.space.tester:create_index('I5',{parts={1,'scalar',2,'integer'}})
box.space.tester:create_index('I6',{parts={1}})
box.space.tester:create_index('I7',{parts={1,2}})
box.space.tester:create_index('I8',{parts={'x'}})
box.space.tester:create_index('I9',{parts={'x','y'}})
box.space.tester:create_index('I10',{parts={{'x'}}})
box.space.tester:create_index('I11',{parts={{'x'},{'y'}}})

Note re storage engine: vinyl supports only the TREE index type, and vinyl secondary indexes must be created before tuples are inserted.