Working with multi-dimensional indexes

Create a multi-dimensional index

POST /_db/{database-name}/_api/index
Creates a multi-dimensional index for the collection collection-name, if it does not already exist. The call expects an object containing the index details.
Path Parameters

  • The name of the database.

Query Parameters

  • The collection name.

HTTP Headers
    Request Body application/json object

    • This attribute controls whether index selectivity estimates are maintained for the index. Not maintaining index selectivity estimates can have a slightly positive impact on write performance.

      The downside of turning off index selectivity estimates is that the query optimizer is not able to determine the usefulness of different competing indexes in AQL queries when there are multiple candidate indexes to choose from.

      The estimates attribute is optional and defaults to true if not set. It has no effect on indexes other than persistent, mdi, and mdi-prefixed. It cannot be disabled for non-unique mdi indexes because they have a fixed selectivity estimate of 1.

    • must be equal to "double". Currently only doubles are supported as values.

    • An array of attribute names used for each dimension. Array expansions are not allowed.

    • You can set this option to true to create the index in the background, which will not write-lock the underlying collection for as long as if the index is built in the foreground. The default value is false.

    • An easy-to-remember name for the index to look it up or refer to it in index hints. Index names are subject to the same character restrictions as collection names. If omitted, a name is auto-generated so that it is unique with respect to the collection, e.g. idx_832910498.

    • Requires type to be "mdi-prefixed", and prefixFields needs to be set in this case.

      An array of attribute names used as search prefix. Array expansions are not allowed.

    • If true, then create a sparse index.

    • The optional storedValues attribute can contain an array of paths to additional attributes to store in the index. These additional attributes cannot be used for index lookups or for sorting, but they can be used for projections. This allows an index to fully cover more queries and avoid extra document lookups.

      You can have the same attributes in storedValues and fields as the attributes in fields cannot be used for projections, but you can also store additional attributes that are not listed in fields. Attributes in storedValues cannot overlap with the attributes specified in prefixFields. There is no reason to store them in the index because you need to specify them in queries in order to use mdi-prefixed indexes.

      You cannot create multiple multi-dimensional indexes with the same sparse, unique, fields and (for mdi-prefixed indexes) prefixFields attributes but different storedValues settings. That means the value of storedValues is not considered by index creation calls when checking if an index is already present or needs to be created.

      In unique indexes, only the index attributes in fields and (for mdi-prefixed indexes) prefixFields are checked for uniqueness. The index attributes in storedValues are not checked for their uniqueness.

      Non-existing attributes are stored as null values inside storedValues.

      The maximum number of attributes in storedValues is 32.

    • must be equal to "mdi" or "mdi-prefixed".

    • if true, then create a unique index.

    Responses

    • If the index already exists, then a HTTP 200 is returned.

    • If the index does not already exist and could be created, then a HTTP 201 is returned.

    • If the index definition is invalid, then a HTTP 400 is returned.

    • If the collection-name is unknown, then a HTTP 404 is returned.

    Examples

    Creating a multi-dimensional index

    curl -X POST --header 'accept: application/json' --data-binary @- --dump - 'http://localhost:8529/_api/index?collection=intervals' <<'EOF'
{
  "type": "mdi",
  "fields": [
    "from",
    "to"
  ],
  "fieldValueTypes": "double"
}
EOF
    Show output

    Creating a prefixed multi-dimensional index

    curl -X POST --header 'accept: application/json' --data-binary @- --dump - 'http://localhost:8529/_api/index?collection=intervals' <<'EOF'
{
  "type": "mdi-prefixed",
  "fields": [
    "from",
    "to"
  ],
  "fieldValueTypes": "double",
  "prefixFields": [
    "year",
    "month"
  ]
}
EOF
    Show output