# Index Management

Indexing is an important method to guarantee database performance and the Mini Program user experience. We should index all fields that are used as query conditions. You can create an index on the console, and the fields of each collection can be individually indexed.

# Single Field Index

For fields that need to be filtered as query conditions, we can create single-field indexes. To index nested fields, you can use "dot notation" to connect the names of nested fields with dots. For example, to index the field "color" in records of the following format, you can use the style.color expression.

{
  "_id": '',
  "style": {
    "color": ''
  }
}

When setting a single field index, it doesn't matter if you specify an ascending or descending sort order. To query sorted index fields, the database can sort the fields correctly no matter if the index is set as ascending or descending.

# Composite Index

A composite index is an index that contains multiple fields. When the fields used by the query conditions are included in all fields or prefix fields defined by the index, the index is hit to optimize the query performance. An index prefix is the first one or more fields defined in the fields of a composite index. If the composite index A, B, C is defined in the fields A, B, and C, then A and A, B are both prefixes of this index.

A composite index has the following features:

1. The field order determines the index effect

When we define a composite index, different field orders can lead to different index effects. For example, when indexing the two fields A and B, defining a composite index as A, B is different from defining a composite index as B, A. When defining a composite index as A, B, the index will be sorted by field A and then by field B. Therefore, when the composite index is set as A, B, a query of field A can hit A, B even if field A is not indexed separately. Note that, in this case, a query of field B is unable to hit index A, B, as B is not one of the prefixes of A, B.

2. The field sort determines whether a sort query can hit the index

Let's set the following indexes for fields A and B:

A: Ascending order
B: Descending order

When our query needs to sort A and B, we can specify the sort result as A ascending B descending or A descending B ascending, but we cannot specify it as A ascending B ascending or A descending B descending.

# Index Attributes

# Uniqueness limit

When creating an index, you can choose to add a uniqueness limit. An index with a uniqueness limit requires that the records in an indexed collection must have unique indexed field values. Assume we have an index I with a uniqueness limit, its index fields are <F1, F2, ..., Fn>, and the collection S contains two arbitrary records R1 and R2. In this case, the record values must satisfy the following conditions: R1.F1 != R2.F1 && R1.F2 != R2.F2 && ... && R1.Fn != R2.Fn. Note that, if a given field does not exist in a record, it takes a default value of null if this field is indexed. If the index has the uniqueness limit, two or more records cannot have a null value or lack of the field.

When creating an index, select Unique as the index attribute to add the uniqueness limit.