Skip to main content

Indexes

Indexes are used to speed up the query process. Without indexes, BuiltAPI must scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, BuiltAPI can use the index to limit the number of documents it must inspect.

Restrictions​

  • Maximum Indexes: Maximum of 64 indexes can be created on a single collection.

Use Cases​

Indexes can be used for various purposes such as:

  • Query Performance: Improve query performance by creating indexes on fields that are frequently queried.
  • Data Integrity: Ensure data integrity by creating unique indexes on fields that should be unique.
  • Full-Text Search: Create "text" indexes to enable full-text search on text fields.
  • Quick Sorting: Create indexes on fields that are used for sorting.
  • GeoSpatial Queries: Create "2dsphere" or "2d" indexes on GeoJSON fields to enable GeoSpatial queries.
  • Delete records after specified time: Create "TTL" indexes to automatically delete documents after a specified time.

Create Index​

1) Open "Indexes" tab on selected Entity indexes tab

Here you can see the list of existing indexes and create a new index. There are always one default index created on the "_id" field.

2) Click on "Create Index" button to open "Create Index" window create index

To create an index we must specify the following:

  • Keys: Set the fields on which the index will be created. You can create indexes on single or multiple fields. To create index, use the following prototype: { <field>: <sortOrder> } Example:
{
  "data.asset_name": 1
}

This will create an index on the "asset_name" field

  • Unique: Check this box to create a unique index. Unique indexes ensure that the indexed fields do not store duplicate values. If you create a unique index on a field, BuiltAPI will reject any insert or update operation that attempts to create a duplicate value in that field.

  • Sparse: Check this box to create a sparse index. Sparse indexes only contain entries for documents that have the indexed field. If the field is missing in a document, the document will not be indexed. It is useful for indexing fields that may not exist in every document.

  • expireAfterSeconds: Optional. Set the time in seconds after which the index should automatically delete the document. TTL indexes can be set only on single-field indexes

Available Index Types​

We use MongoDB as a database engine, so we support the following index types:

Single field index​

Single field indexes store information from a single field in a collection. By default, all Entities have an index on the _id field. You can add additional indexes to speed up important queries and operations.

When you create an index, you specify:

  • The field on which to create the index.
  • The sort order for the indexed values (ascending or descending).
    • A sort order of 1 sorts values in ascending order.
    • A sort order of -1 sorts values in descending order.

Keys Example:

{
    "data.asset.name": 1
}

more information about single field indexes you can find here

TTL​

You can set expireAfterSeconds to create a TTL index. TTL indexes automatically delete documents from a collection after a certain amount of time has passed. This is useful for certain types of data, such as session data or event logs, that should only persist in a database for a limited amount of time.

Only single-field indexes can be TTL indexes.

Compound index​

Compound indexes collect and sort data from two or more fields in each document in a collection. Data is grouped by the first field in the index and then by each subsequent field.

Keys Example:

{
    "data.userid": 1,
    "data.score": -1
}

more information about compound indexes you can find here

Multikey index​

Multikey indexes support queries against arrays. If you index a field that holds an array value, MongoDB creates separate index entries for every element of the array.

Entity records example:

{
    "_id": "1b42d497-21ba-4dfa-a4bd-05c571c7c49d",
    "meta": {
        "updated": "true",
        "created_at": "2021-01-01T00:00:00Z",
        "updated_at": "2021-01-01T00:00:00Z"
    }
    "data": {
        "userid":"xyz",
        "address": [
            {
                "street": "2 Avenue",
                "city": "New York"
            },
            {
                "street": "5 Avenue",
                "city": "New York"
            }
        ]
    }
}

Keys example:
```json
{
    "data.address.city": 1
}

Use Cases If your application frequently queries a field that contains an array value, a multikey index improves performance for those queries.

more information about multikey indexes you can find here

Text index​

Text indexes support text search queries on fields containing string content. Text indexes improve performance when searching for specific words or phrases within string content.

A collection can only have one text index, but that index can cover multiple fields.

Keys example:

{
    "data.description": "text"
}

more information about text indexes you can find here

Wildcard index​

MongoDB supports flexible schemas, meaning document field names may differ within a collection. Use wildcard indexes to support queries against arbitrary or unknown fields.

To create a wildcard index, use the wildcard specifier ($**) as the index key:

Keys example:

{
    "$**": 1
}

Use Cases Only use wildcard indexes when the fields you want to index are unknown or may change. Wildcard indexes don't perform as well as targeted indexes on specific fields. If your collection contains arbitrary field names that prevent targeted indexes, consider remodeling your schema to have consistent field names.

more information about wildcard indexes you can find here

Geospatial index​

Geospatial indexes support queries on data stored as GeoJSON objects or legacy coordinate pairs. You can use geospatial indexes to improve performance for queries on geospatial data or to run certain geospatial queries.

MongoDB provides two types of geospatial indexes:

  • 2dsphere Indexes, which support queries that interpret geometry on a sphere.
  • 2d Indexes, which support queries that interpret geometry on a flat surface.

Use Cases If your application frequently queries a field that contains geospatial data, you can create a geospatial index to improve performance for those queries.

index template: { <location field> : "2dsphere" } or { <location field> : "2d" }

Records data example:

[
   {
      "loc": { "type": "Point", "coordinates": [ -73.97, 40.77 ] },
      "name": "Central Park",
      "category" : "Park"
   },
   {
      "loc": { "type": "Point", "coordinates": [ -73.88, 40.78 ] },
      "name": "La Guardia Airport",
      "category": "Airport"
   },
   {
      "loc": { "type": "Point", "coordinates": [ -1.83, 51.18 ] },
      "name": "Stonehenge",
      "category" : "Monument"
   }
]

Keys example:

{
    "data.loc": "2dsphere"
}