Creating Indexes

Collection indexes can be created using one or more fields. The method used to create these indexes is mysqlx.Collection.create_index() and the following sections describes the required arguments for the function and some examples of use.

Arguments for mysqlx.Collection.create_index()

To use the mysqlx.Collection.create_index() we need to specify the name of the index to be created and the members to be part of the index, in addition for each member we need to specify the type of data that holds the field in the document and if it is required or not. Fields marked as required must appear on each document in the collection.

{"fields": [{"field": member_path, # required str
             "type": member_type, # required str, must be a valid type
             "required": member_required, # optional, True or (default) False
             "collation": collation, # optional str only for TEXT field type
             "options": options, # optional (int) only for GEOJSON field type
             "srid": srid}, # optional (int) only for GEOJSON field type
             # {... more members,
             #      repeated as many times
             #      as needed}
             ],
 "type": type} # optional, SPATIAL or (default) INDEX

The valid types for the type field are:

• 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)

Note: The use of type GEOJSON, requires the index type to be set to SPATIAL.

Using mysqlx.Collection.create_index()

First we need to get a session and a schema.

import mysqlx

# Connect to server on localhost
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'mike',
    'password': 's3cr3t!'
})

schema = session.get_schema('test')

Next step is create a sample collection.

# Create 'collection_GOT' in schema
schema.create_collection('collection_GOT')

# Get 'collection_GOT' from schema
collection = schema.get_collection('collection_GOT')

The following example shows how to create a simple index with name index_age that will use a field age from the document which will hold integer values.

collection.create_index("index_age", {"fields": [{"field": "age",
                                                  "type": "INT"}],
                                      "type":"INDEX"})

The following example shows how to create a multi field index with name index_name that will use the fields family_name and name from the document that will hold small texts. This time the required member has been set to True, which means these fields are required for all the documents in this collection.

collection.create_index("index_name", {"fields": [{"field": "family_name",
                                                   "type": "TEXT(12)",
                                                   "required": True}],
                                       "fields": [{"field": "name",
                                                   "type": "TEXT(12)",
                                                   "required": True}],
                                       "type":"INDEX"})

The following example shows how to create a multi field index with name geojson_name, which will use fields with GEOJSON data, so for this will require the index type to be set to SPATIAL, that will use the fields $.geoField, $.intField, $.floatField and $.dateField. Each field hold the data that compounds the name of the file. Note that by setting SPATIAL to the index type we will require to set for each of these members required to True, which means these fields are required for all the documents in this collection.

collection.create_index("index_age",
                        {"fields": [{"field": "$.geoField",
                                     "type": "GEOJSON",
                                     "required": False, "options": 2,
                                     "srid": 4326},
                                    {"field": "$.intField", "type": "INT",
                                     "required": True},
                                    {"field": "$.floatField",
                                     "type": "FLOAT",
                                     "required": True},
                                    {"field": "$.dateField",
                                     "type": "DATE", "required": True}],
                         "type" : "SPATIAL"})