Working with Indexes

    A specific index of a collection can be accessed using its index handle orindex identifier as follows:

    For example: Assume that the index handle, which is stored in the _idattribute of the index, is demo/362549736 and the index was created in a collectionnamed demo. Then this index can be accessed as:

    1. db.demo.index("demo/362549736");

    Because the index handle is unique within the database, you can leave out thecollection and use the shortcut:

    returns information about the indexesgetIndexes()

    Returns an array of all indexes defined for the collection.

    Note that _key implicitly has an index assigned to it.

    1. arangosh> db.test.ensureHashIndex("hashListAttribute",
    2. ........> "hashListSecondAttribute.subAttribute");
    3. arangosh> db.test.getIndexes();

    Show execution results

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "hashListAttribute", 
    5.     "hashListSecondAttribute.subAttribute" 
    6.   ], 
    7.   "id" : "test/15607", 
    8.   "isNewlyCreated" : true, 
    9.   "selectivityEstimate" : 1, 
    10.   "sparse" : false, 
    11.   "type" : "hash", 
    12.   "unique" : false, 
    13.   "code" : 201 
    14. }
    15. [ 
    16.   { 
    17.     "fields" : [ 
    18.       "_key" 
    19.     ], 
    20.     "id" : "test/0", 
    21.     "selectivityEstimate" : 1, 
    22.     "sparse" : false, 
    23.     "type" : "primary", 
    24.     "unique" : true 
    25.   }, 
    26.   { 
    27.     "deduplicate" : true, 
    28.     "fields" : [ 
    29.       "skiplistAttribute" 
    30.     ], 
    31.     "id" : "test/15601", 
    32.     "sparse" : false, 
    33.     "type" : "skiplist", 
    34.     "unique" : true 
    35.   }, 
    36.   { 
    37.     "deduplicate" : true, 
    38.     "fields" : [ 
    39.       "skiplistUniqueAttribute" 
    40.     ], 
    41.     "id" : "test/15604", 
    42.     "sparse" : false, 
    43.     "type" : "skiplist", 
    44.     "unique" : true 
    45.   }, 
    46.   { 
    47.     "deduplicate" : true, 
    48.     "fields" : [ 
    49.       "hashListAttribute", 
    50.       "hashListSecondAttribute.subAttribute" 
    51.     ], 
    52.     "id" : "test/15607", 
    53.     "selectivityEstimate" : 1, 
    54.     "sparse" : false, 
    55.     "type" : "hash", 
    56.     "unique" : false 
    57.   } 
    58. ]

    Hide execution results

    Creating an index

    Indexes should be created using the general method ensureIndex. Thismethod obsoletes the specialized index-specific methods ensureHashIndex,ensureSkiplist, ensureUniqueConstraint etc.ensures that an index existscollection.ensureIndex(index-description)

    Ensures that an index according to the index-description exists. Anew index will be created if none exists with the given description.

    The index-description must contain at least a type attribute.Other attributes may be necessary, depending on the index type.

    type can be one of the following values:

    • hash: hash index
    • skiplist: skiplist index
    • fulltext: fulltext index
    • geo1: geo index, with one attribute
    • geo2: geo index, with two attributessparse can be true or false.

    For hash, and skiplist the sparsity can be controlled, fulltext and _geo_are by definition.

    unique can be true or false and is supported by hash or skiplist

    deduplicate can be true or false and is supported by array indexes oftype hash or skiplist. It controls whether inserting duplicate index values from the same document into a unique array index will lead to a unique constrainterror or not. The default value is true, so only a single instance of eachnon-unique index value will be inserted into the index per document. Trying toinsert a value into the index that already exists in the index will always fail,regardless of the value of this attribute.

    Examples

    Show execution results

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "a" 
    5.   ], 
    6.   "id" : "test/15551", 
    7.   "isNewlyCreated" : true, 
    8.   "selectivityEstimate" : 1, 
    9.   "sparse" : true, 
    10.   "type" : "hash", 
    11.   "unique" : false, 
    12.   "code" : 201 
    13. }
    14. { 
    15.   "deduplicate" : true, 
    16.   "fields" : [ 
    17.     "a", 
    18.     "b" 
    19.   ], 
    20.   "isNewlyCreated" : true, 
    21.   "selectivityEstimate" : 1, 
    22.   "sparse" : false, 
    23.   "type" : "hash", 
    24.   "unique" : true, 
    25.   "code" : 201 
    26. }

    Hide execution results

    drops an indexcollection.dropIndex(index)

    Drops the index. If the index does not exist, then false isreturned. If the index existed and was dropped, then true isreturned. Note that you cannot drop some special indexes (e.g. the primaryindex of a collection or the edge index of an edge collection).

    Same as above. Instead of an index an index handle can be given.

    1. arangosh> db.example.ensureSkiplist("a", "b");
    2. arangosh> var indexInfo = db.example.getIndexes();
    3. arangosh> indexInfo;
    4. arangosh> db.example.dropIndex(indexInfo[0])
    5. arangosh> db.example.dropIndex(indexInfo[1].id)
    6. arangosh> indexInfo = db.example.getIndexes();

    Show execution results

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "a", 
    5.     "b" 
    6.   ], 
    7.   "id" : "example/15412", 
    8.   "isNewlyCreated" : true, 
    9.   "sparse" : false, 
    10.   "type" : "skiplist", 
    11.   "unique" : false, 
    12.   "code" : 201 
    13. }
    14. [ 
    15.   { 
    16.     "fields" : [ 
    17.       "_key" 
    18.     ], 
    19.     "id" : "example/0", 
    20.     "selectivityEstimate" : 1, 
    21.     "sparse" : false, 
    22.     "type" : "primary", 
    23.     "unique" : true 
    24.   }, 
    25.   { 
    26.     "deduplicate" : true, 
    27.     "fields" : [ 
    28.       "a", 
    29.       "b" 
    30.     ], 
    31.     "id" : "example/15412", 
    32.     "sparse" : false, 
    33.     "type" : "skiplist", 
    34.     "unique" : false 
    35.   } 
    36. ]
    37. false
    38. true
    39. [ 
    40.   { 
    41.     "fields" : [ 
    42.       "_key" 
    43.     ], 
    44.     "id" : "example/0", 
    45.     "selectivityEstimate" : 1, 
    46.     "sparse" : false, 
    47.     "type" : "primary", 
    48.     "unique" : true 
    49.   } 
    50. ]

    Hide execution results

    Load Indexes into Memory

    Loads all indexes of this collection into Memory.collection.loadIndexesIntoMemory()

    This function tries to cache all index entriesof this collection into the main memory.Therefore it iterates over all indexes of the collectionand stores the indexed values, not the entire document data,in memory.All lookups that could be found in the cache are much fasterthan lookups not stored in the cache so you get a nice performance boost.It is also guaranteed that the cache is consistent with the stored data.

    For the time being this function is only useful on RocksDB storage engine,as in MMFiles engine all indexes are in memory anyways.

    On RocksDB this function honors all memory limits, if the indexes you wantto load are smaller than your memory limit this function guarantees that mostindex values are cached.If the index is larger than your memory limit this function will fill up valuesup to this limit and for the time being there is no way to control which indexesof the collection should have priority over others.

    1. arangosh> db.example.loadIndexesIntoMemory();

    Hide execution results

    finds an indexdb._index(index-handle)

    Returns the index with index-handle or null if no such index exists.

    1. arangosh> db.example.ensureIndex({ type: "skiplist", fields: [ "a", "b" ] });
    2. arangosh> var indexInfo = db.example.getIndexes().map(function(x) { return x.id; });
    3. arangosh> indexInfo;
    4. arangosh> db._index(indexInfo[0])
    5. arangosh> db._index(indexInfo[1])

    Show execution results

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "a", 
    5.     "b" 
    6.   ], 
    7.   "id" : "example/9571", 
    8.   "isNewlyCreated" : true, 
    9.   "sparse" : false, 
    10.   "type" : "skiplist", 
    11.   "unique" : false, 
    12.   "code" : 201 
    13. }
    14. [ 
    15.   "example/0", 
    16.   "example/9571" 
    17. ]
    18. { 
    19.   "fields" : [ 
    20.     "_key" 
    21.   ], 
    22.   "id" : "example/0", 
    23.   "selectivityEstimate" : 1, 
    24.   "sparse" : false, 
    25.   "type" : "primary", 
    26.   "unique" : true, 
    27.   "code" : 200 
    28. }
    29.   "deduplicate" : true, 
    30.   "fields" : [ 
    31.     "a", 
    32.     "b" 
    33.   ], 
    34.   "id" : "example/9571", 
    35.   "sparse" : false, 
    36.   "type" : "skiplist", 
    37.   "unique" : false, 
    38.   "code" : 200 
    39. }

    Hide execution results

    Dropping an index via a database handle

    drops an indexdb._dropIndex(index)

    Drops the index. If the index does not exist, then false isreturned. If the index existed and was dropped, then true isreturned.

    db._dropIndex(index-handle)

    Drops the index with index-handle.

    1. arangosh> db.example.ensureIndex({ type: "skiplist", fields: [ "a", "b" ] });
    2. arangosh> var indexInfo = db.example.getIndexes();
    3. arangosh> indexInfo;
    4. arangosh> db._dropIndex(indexInfo[0])
    5. arangosh> db._dropIndex(indexInfo[1].id)
    6. arangosh> indexInfo = db.example.getIndexes();

    Show execution results

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "a", 
    5.     "b" 
    6.   ], 
    7.   "id" : "example/16191", 
    8.   "isNewlyCreated" : true, 
    9.   "sparse" : false, 
    10.   "type" : "skiplist", 
    11.   "unique" : false, 
    12.   "code" : 201 
    13. }
    14. [ 
    15.   { 
    16.     "fields" : [ 
    17.       "_key" 
    18.     ], 
    19.     "id" : "example/0", 
    20.     "selectivityEstimate" : 1, 
    21.     "sparse" : false, 
    22.     "type" : "primary", 
    23.     "unique" : true 
    24.   }, 
    25.   { 
    26.     "deduplicate" : true, 
    27.     "fields" : [ 
    28.       "a", 
    29.       "b" 
    30.     ], 
    31.     "id" : "example/16191", 
    32.     "sparse" : false, 
    33.     "type" : "skiplist", 
    34.     "unique" : false 
    35.   } 
    36. ]
    37. false
    38. true
    39. [ 
    40.   { 
    41.     "fields" : [ 
    42.       "_key" 
    43.     ], 
    44.     "id" : "example/0", 
    45.     "selectivityEstimate" : 1, 
    46.     "sparse" : false, 
    47.     "type" : "primary", 
    48.     "unique" : true 
    49.   } 
    50. ]

    Hide execution results

    finds an index

    So you’ve created an index, and since its maintainance isn’t for free,you definitely want to know whether your query can utilize it.

    You can use explain to verify whether skiplists or hash indexes are used (if you omit you will get nice colors in ArangoShell):

    1. { 
    2.   "deduplicate" : true, 
    3.   "fields" : [ 
    4.     "a", 
    5.     "b" 
    6.   ], 
    7.   "id" : "example/9581", 
    8.   "isNewlyCreated" : true, 
    9.   "sparse" : false, 
    10.   "type" : "skiplist", 
    11.   "unique" : false, 
    12.   "code" : 201 
    13. }
    14. Query string:
    15.  FOR doc IN example FILTER doc.a < 23 RETURN doc
    16.  
    17. Execution plan:
    18.  Id   NodeType        Est.   Comment
    19.   1   SingletonNode      1   * ROOT
    20.   6   IndexNode          1     - FOR doc IN example   /* skiplist index scan */
    21.   5   ReturnNode         1       - RETURN doc
    22.  
    23. Indexes used:
    24.  By   Type       Collection   Unique   Sparse   Selectivity   Fields         Ranges
    25.   6   skiplist   example      false    false            n/a   [ `a`, `b` ]   (doc.`a` < 23)
    26.  
    27. Optimization rules applied:
    28.  Id   RuleName
    29.   1   use-indexes
    30.   2   remove-filter-covered-by-index

    Hide execution results