Using indexes

Creating an index

It is mandatory to create an index for a space before trying to insert tuples into the space, or select tuples from the space.

The simple index-creation operation is:

box.space.space-name:create_index('index-name')

This creates a unique TREE index on the first field of all tuples (often called “Field#1”), which is assumed to be numeric.

A recommended design pattern for a data model is to base primary keys on the first fields of a tuple. This speeds up tuple comparison due to the specifics of data storage and the way comparisons are arranged in Tarantool.

The simple SELECT request is:

box.space.space-name:select(value)

This looks for a single tuple via the first index. Since the first index is always unique, the maximum number of returned tuples will be 1. You can call select() without arguments, and it will return all tuples. Be careful! Using select() for huge spaces hangs your instance.

An index definition may also include identifiers of tuple fields and their expected types. See allowed indexed field types in section Details about indexed field types:

box.space.space-name:create_index(index-name, {type = 'tree', parts = {{field = 1, type = 'unsigned'}}}

Space definitions and index definitions are stored permanently in Tarantool’s system spaces _space and _index.

Index operations

Index operations are automatic: if a data manipulation request changes a tuple, then it also changes the index keys defined for the tuple.

1. Create a sample space named bands:

   bands = box.schema.space.create('bands')
   

2. Format the created space by specifying field names and types:

   box.space.bands:format({
       { name = 'id', type = 'unsigned' },
       { name = 'band_name', type = 'string' },
       { name = 'year', type = 'unsigned' }
   })
   

3. Create the primary index (named primary):

   box.space.bands:create_index('primary', { parts = { 'id' } })
   

   This index is based on the id field of each tuple.

4. Insert some tuples into the space:

   box.space.bands:insert { 1, 'Roxette', 1986 }
   box.space.bands:insert { 2, 'Scorpions', 1965 }
   box.space.bands:insert { 3, 'Ace of Base', 1987 }
   box.space.bands:insert { 4, 'The Beatles', 1960 }
   box.space.bands:insert { 5, 'Pink Floyd', 1965 }
   box.space.bands:insert { 6, 'The Rolling Stones', 1962 }
   box.space.bands:insert { 7, 'The Doors', 1965 }
   box.space.bands:insert { 8, 'Nirvana', 1987 }
   box.space.bands:insert { 9, 'Led Zeppelin', 1968 }
   box.space.bands:insert { 10, 'Queen', 1970 }
   

5. Create secondary indexes:

   -- Create a unique secondary index --
   box.space.bands:create_index('band', { parts = { 'band_name' } })
   
   -- Create a non-unique secondary index --
   box.space.bands:create_index('year', { parts = { { 'year' } }, unique = false })
   

6. Create a multi-part index with two parts:

   box.space.bands:create_index('year_band', { parts = { { 'year' }, { 'band_name' } } })
   

There are the following SELECT variations:

• The search can use comparisons other than equality:

  -- Select maximum 3 tuples with the key value greater than 1965 --
  select_greater = bands.index.year:select({ 1965 }, { iterator = 'GT', limit = 3 })
  --[[
  ---
  - - [9, 'Led Zeppelin', 1968]
    - [10, 'Queen', 1970]
    - [1, 'Roxette', 1986]
  ...
  --]]
  

  The comparison operators are:

  • LT for “less than”
  • LE for “less than or equal”
  • GT for “greater”
  • GE for “greater than or equal”
  • EQ for “equal”
  • REQ for “reversed equal”

  Value comparisons make sense if and only if the index type is TREE. The iterator types for other types of indexes are slightly different and work differently. See details in section Iterator types.

  Note that we don’t use the name of the index, which means we use primary index here.

  This type of search may return more than one tuple. The tuples will be sorted in descending order by key if the comparison operator is LT or LE or REQ. Otherwise they will be sorted in ascending order.

• The search can use a secondary index.

  -- Select a tuple by the specified secondary key value --
  select_secondary = bands.index.band:select { 'The Doors' }
  --[[
  ---
  - - [7, 'The Doors', 1965]
  ...
  --]]
  

• Partial key search: The search may be for some key parts starting with the prefix of the key. Note that partial key searches are available only in TREE indexes.

  -- Select tuples by the specified partial key value --
  select_multipart_partial = bands.index.year_band:select { 1965 }
  --[[
  ---
  - - [5, 'Pink Floyd', 1965]
    - [2, 'Scorpions', 1965]
    - [7, 'The Doors', 1965]
  ...
  --]]
  

• The search can be for all fields, using a table as the value:

  -- Select a tuple by the specified multi-part secondary key value --
  select_multipart = bands.index.year_band:select { 1960, 'The Beatles' }
  --[[
  ---
  - - [4, 'The Beatles', 1960]
  ...
  --]]