Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  ...  /  Indexing Collections

4.2.3 Indexing Collections

To make large collections of documents more efficient to navigate you can create an index based on one or more fields found in the documents in the collection. This section describes how to index a collection.

Creating an Index

Collection indexes are ordinary MySQL indexes on virtual columns that extract data from the documents in the collection. Currently MySQL cannot index JSON values directly, therefore to enable indexing of a collection you provide a JSON document which specifies the document's fields used by the index. You pass the JSON document defining the index as the IndexDefinition parameter to the Collection.createIndex(name, IndexDefinition) method. For example, to create a mandatory integer type index based on the field count:

myCollection.createIndex("count", {fields:[{"field": "$.count", "type":"INT", required:true}]});

To create an index based on a field such as a zip code, trimming the result to 10 characters:

myCollection.createIndex("zip", {fields: [{field: "$.zip", type: "TEXT(10)"}]})

See Defining an Index for information about the format of the JSON document used to define fields as MySQL types, and the currently supported MySQL types.

The Collection.createIndex() method fails with an error if an index with the same name already exists or if index definition is not correctly formed. The name parameter is required and must be a valid index name as accepted by SQL command CREATE INDEX.

To remove an existing index use the collection.dropIndex(string name) method. This would delete the index with the name passed in, and the operation silently succeeds if the named index does not exist.

As the indexes of a collection are stored as virtual columns, to verify a created index use the SHOW INDEX statement. For example to use this SQL from MySQL Shell:

session.runSql('SHOW INDEX FROM mySchema.myCollection');

Defining an Index

To create an index based on the documents in a collection you need to create an IndexDefinition JSON document. This section explains the valid fields you can use in such a JSON document to define an index.

To define a document field to index a collection on, the type of that field must be uniform across the whole collection. In other words the type must be consistent. The JSON document used for defining an index, such as {fields: [{field: '$.username', type: 'TEXT'}]}, can contain the following:

  • fields: an array of at least one IndexField object, each of which describes a JSON document field to be included in the index.

    A single IndexField description consists of the following fields:

    • field: a string with the full document path to the document member or field to be indexed

    • type: a string with one of the supported column types to map the field to (see Field Data Types ). For numeric types, the optional UNSIGNED keyword can follow. For the TEXT type you can define the length to consider for indexing.

    • required: an optional boolean, set to true if the field is required to exist in the document. Defaults to false for all types except GEOJSON, which defaults to true.

    • options: an optional integer, used as special option flags to use when decoding GEOJSON data.

    • srid: an optional integer, srid value to use when decoding GEOJSON data.

  • type: an optional string which defines the type of index. One of INDEX or SPATIAL. The default is INDEX and can be omitted.

For example to create an index based on multiple fields, issue:

myCollection.createIndex('myIndex', {fields: [{field: '$.myField', type: 'TEXT'}, //
{field: '$.myField2', type: 'TEXT(10)'}, {field: '$.myField3', type: 'INT'}]})

Including any other fields in an IndexDefinition or IndexField JSON document which is not described here causes collection.createIndex() to fail with an error.

If index type is not specified, or is set to INDEX then the resulting index is created in the same way as would be created after issuing CREATE INDEX. If index type is set to SPATIAL then the created index is the same as would be created after issuing CREATE INDEX with the SPATIAL keyword, see SPATIAL Index Optimization and Creating Spatial Indexes. For example

myCollection.createIndex('myIndex', //
{fields: [{field: '$.myGeoJsonField', type: 'GEOJSON', required: true}], type:'SPATIAL'})
Important

When using the SPATIAL type of index the required field cannot be set to false in IndexField entries.

The values of indexed fields are converted from JSON to the type specified in the IndexField description using standard MySQL type conversions (see Type Conversion in Expression Evaluation), except for the GEOJSON type which uses the ST_GeomFromGeoJSON() function for conversion. This means that when using a numeric type in an IndexField description and the actual field value is non-numeric, it is converted to 0.

The options and srid fields in IndexField can only be present if type is set to GEOJSON. If present, they are used as parameters for ST_GeomFromGeoJSON() when converting GEOJSON data into MySQL native GEOMETRY values.

Important

Currently fields that are JSON arrays are not supported in the index. Specifying a field that contains array data does not generate an error from the server but is unsupported and does not function correctly.

Field Data Types

The following data types are supported for document fields. Type descriptions are case insensitive.


User Comments
Sign Up Login You must be logged in to post a comment.