Similar to indexes on relational tables, it is possible to define indexes on document collections to improve efficiency of accessing documents in the collection. To define such an index call X DevAPI method Collection::createIndex() or X DevAPI for C function mysqlx_collection_create_index() and pass a JSON document which specifies the index. The format of the index specification document is described below. The index can include one or more fields within indexed documents and it is possible to specify whether indexed fields are required to always exist. For indexing to work, values inside documents must be converted to SQL values. For that reason you must specify an SQL type to be used for each indexed field.

Index Specification

An example index specification might look as follows:

{
  "type": "INDEX",
  "fields": [
    { "field": "$.zip", "type": "TEXT(10)" },
    { "field": "$.count", "type": "INT UNSIGNED" }
  ]
}

Specification for an index on a document collection is a JSON document with the following members:

fields (an array of objects): Describes document fields to be included in the index (see below)
type (a string, optional): The type of index. One of "INDEX" or "SPATIAL". Default is "INDEX" and may be omitted.

A single field description consists of the following members:

field (a string): A path to the document field to be indexed.
type (a string): One of the supported SQL column types to map the field into (see below for a list). For numeric types, the optional UNSIGNED keyword may follow. For the TEXT type, the length to consider for indexing must be specified.
required (a Boolean, optional): True if the field is required to exist in the document. Defaults to false, except for GEOJSON where it defaults to true
options (an integer, optional): Special option flags for use when decoding GEOJSON data (see below).
srid (an integer, optional): A "srid" value used when decoding GEOJSON data (see below).

It is an error to include other fields not described above in an index specification.

If the index type is not specified, or is set to "INDEX" then a plain index is created as by SQL command "CREATE INDEX ...". If the index type is set to "SPATIAL" then the created index is like one created by SQL command "CREATE SPATIAL INDEX ...", see https://dev.mysql.com/doc/refman/en/spatial-index-optimization.html and https://dev.mysql.com/doc/refman/en/creating-spatial-indexes.html. In this case it is not allowed to set the "required" field to false in field descriptions.

Values of indexed fields are converted from JSON to the type specified in the index field description using standard MySQL type conversions (see https://dev.mysql.com/doc/refman/en/type-conversion.html). Note that this means that, for example, when using a numeric type in an index field description and the actual field value is non-numeric, it will be usually converted to 0.

For index fields with GEOJSON type MySQL function ST_GeomFromGeoJSON() is used for conversion (see https://dev.mysql.com/doc/refman/en/spatial-geojson-functions.html#function_st-geomfromgeojson). In this case, the "options" and "srid" fields might be present in the field description and if this is the case they are used as parameters for ST_GeomFromGeoJSON() during conversion.

Field Data Types

The following data types may be used for document fields. Type descriptions are case insensitive.

INT [UNSIGNED]
TINYINT [UNSIGNED]
SMALLINT [UNSIGNED]
MEDIUMINT [UNSIGNED]
INTEGER [UNSIGNED]
BIGINT [UNSIGNED]
REAL [UNSIGNED]
FLOAT [UNSIGNED]
DOUBLE [UNSIGNED]
DECIMAL [UNSIGNED]
NUMERIC [UNSIGNED]
DATE
TIME
TIMESTAMP
DATETIME
TEXT(length)
GEOJSON