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.
Collection indexes are ordinary MySQL indexes on virtual columns that extract data from the documents in the collection. Because MySQL cannot index JSON values directly, to enable indexing of a collection, you provide a JSON document that specifies the document's fields to be used by the index. You pass the JSON document defining the index as the IndexDefinition
parameter to the Collection.createIndex(
method. This generic example (actual syntax might vary for different programming languages) shows how to create a mandatory integer type index based on the field name
, IndexDefinition
)count
:
myCollection.createIndex("count", {fields:[{"field": "$.count", "type":"INT", required:true}]});
This example shows how to create an index based on a text field: a zip code in this case. For a text field, you must specify a prefix length for the index, as required by MySQL Server:
myCollection.createIndex("zip", {fields: [{field: "$.zip", type: "TEXT(10)"}]})
See Defining an Index for information on the format of IndexDefinition
and on the supported field types for indexing.
The Collection.createIndex()
method fails with an error if an index with the same name already exists or if the index definition is not correctly formed. The name parameter is required and must be a valid index name as accepted by the SQL statement CREATE INDEX.
To remove an existing index use the collection.dropIndex(string name)
method. This would delete the index with the passed name, and the operation silently succeeds if the named index does not exist.
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');
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 oneIndexField
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 indexedtype
: a string for one of the supported column types to map the field to (see Field Data Types ). For numeric types, the optionalUNSIGNED
keyword can follow. For theTEXT
type you must define the length to consider for indexing (the prefix length).required
: an optional boolean that should be set totrue
if the field is required to exist in the document. Defaults tofalse
for all types exceptGEOJSON
, which defaults totrue
.options
: an optional integer that is used as a special option flag when decodingGEOJSON
data (see the description for ST_GeomFromGeoJSON() for details).srid
: an optional integer to be used as the srid value when decodingGEOJSON
data (see the description for ST_GeomFromGeoJSON() for details).array
: (for MySQL 8.0.17 and later) an optional boolean that is set totrue
if the field contains arrays. The default value isfalse
. See Indexing Array Fields for details.Important
For MySQL 8.0.16 and earlier, 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 the index does not function correctly.
type
: an optional string that defines the type of index. Value is one ofINDEX
orSPATIAL
. The default isINDEX
and can be omitted.
Including any other fields in an IndexDefinition
or IndexField
JSON document which is not described above 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 it would be created by issuing CREATE INDEX. If index type is set to SPATIAL
then the created index is the same as it would be created by 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.
This is an example to create an index based on multiple fields:
myCollection.createIndex('myIndex', {fields: [{field: '$.myField', type: 'TEXT'}, //{field: '$.myField2', type: 'TEXT(10)'}, {field: '$.myField3', type: 'INT'}]})
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. That means when using a numeric type in an IndexField
description, an actual field value that is non-numeric 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.
The following data types are supported for document fields. Type names are case-insensitive when used in the type
field.
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 (extra options: options, srid)
For MySQL 8.0.17 and later, X DevAPI supports creating indexes based on array fields by setting the boolean array
field in the IndexField
description to true
. For example, to create an index on the emails
array field:
collection.createIndex("emails_idx", // {fields: [{"field": "$.emails", "type":"CHAR(128)", "array": true}]});
The following restrictions apply to creating indexes based on arrays:
For each index, only one indexed field can be an
array
Data types for which index on arrays can be created:
Numeric types: INTEGER
[UNSIGNED]
(INT is NOT supported)Fixed-point types: DECIMAL(m, n) (the precision and scale values are mandatory)
Date and time types: DATE, TIME, and DATETIME
String types: CHAR(n) and BINARY(n); the character or byte length
n
is mandatory (TEXT is NOT supported)