Index Management

Indexes are a key technology for enhancing database query performance. By creating indexes for frequently queried fields, query speed and user experience can be significantly improved.

How to Create an Index

1. Access Cloud Development Platform/Database
2. Go to Index Management: Select the target collection and click the Index Management tab
3. Manage Indexes: Create, delete, or modify index configurations.

Detailed Explanation of Index Types

Supported index types: single-field index, composite index, geospatial index, and unique index.

Single-Field Index

Applicable Scenarios: Query and sort operations targeting a single field

Feature Description:

• Supports nested field indexes, accessing them using "dot notation".
• Allows specifying ascending or descending order.
• Suitable for simple single-condition queries

Example Configuration:

// Raw data structure
{
  "_id": "product_001",
  "name": "iPhone 15",
  "price": 5999,
  "style": {
    "color": "Deep Space Black",
    "storage": "128GB"
  }
}

Index Configuration Example:

Field Path	Index Type	Sort Order	Applicable Queries
name	Single Field	Ascending	Query by product name
price	Single Field	Descending	Sort by price
style.color	Single Field	Ascending	Filter by color

💡 Note: Nested fields use dot notation, such as style.color to access the color field within the style object.

Composite Index

Applicable Scenarios: Multi-field joint queries and complex sorting operations

Core Concept: An index contains multiple fields and supports prefix matching queries.

Sample Data:

{
  "_id": "student_001",
  "name": "Zhang San",
  "age": 20,
  "score": 85,
  "class": "Computer Science"
}

Index Prefix Rule:

Suppose we create a composite index: name + age + score

Index Prefix	Supported Query Combinations	Query Example
name	Query name alone	db.collection('students').where({name: 'Zhang San'})
name, age	Query name + age	db.collection('students').where({name: 'Zhang San', age: 20})
name, age, score	Query all fields	db.collection('students').where({name: 'Zhang San', age: 20, score: 85})

Prefix Matching Principle

The composite index (name, age, score) includes the following prefixes:

• ✅ name - can leverage the index
• ✅ name, age - can leverage the index
• ✅ name, age, score - can leverage the index
• ❌ age - cannot leverage the index (not a prefix)
• ❌ score - cannot leverage the index (not a prefix)
• ❌ age, score - cannot leverage the index (not a prefix)

Key Features of Composite Indexes:

Importance of Field Order

The order of index fields directly affects query performance:

Index Definition	Supported Queries	Unsupported Queries
(name, age)	✅ name query ✅ name + age query	❌ age query alone ❌ age + score query
(age, name)	✅ age query ✅ age + name query	❌ name query alone ❌ name + score query

Impact of Sort Order

The sort order of indexes affects whether they can be leveraged in sort queries:

Index Configuration: age: ascending, score: descending

Query Sort Order	Index Usage	Notes
age: ascending, score: descending	✅ Yes	Exactly matches the index order
age: descending, score: ascending	✅ Yes	Index supports reverse scan
age: ascending, score: ascending	❌ No	Sort order inconsistent
age: descending, score: descending	❌ No	Sort order inconsistent
score: any, age: any	❌ No	Prefix rule not satisfied

Index Configuration: age: ascending, score: ascending

Query Sort Order	Index Usage	Notes
age: ascending, score: ascending	✅ Yes	Exactly matches the index order
age: descending, score: descending	✅ Yes	Index supports reverse scan
age: ascending, score: descending	❌ No	Sort order inconsistent
age: descending, score: ascending	❌ No	Sort order inconsistent

Geospatial Index

Applicable Scenarios: Geolocation-based queries, such as nearby businesses, distance calculations, etc.

Creation Steps:

1. On Cloud Development Platform/Database, select collection.
2. go to the Index Management page
3. Select the geolocation field (e.g., location)
4. Set the index type to "geospatial index"

Query Example:

Mini Program

For detailed documentation, please refer to the Mini Program GEO Query Documentation

const _ = db.command

db.collection('restaurants').where({
  location: _.geoNear({
    geometry: db.Geo.Point(116.4, 39.9),
    minDistance: 1000,
    maxDistance: 5000,
  })
}).get()

Web

const _ = db.command

db.collection('restaurants').where({
  location: _.geoNear({
    geometry: new db.Geo.Point(116.4, 39.9),
    minDistance: 1000,
    maxDistance: 5000,
  })
}).get()

⚠️ Note: geoNear only supports the geometry, maxDistance, and minDistance options. It does not support limiting the number of returned records via an option (for example limit: 10). To limit the results, chain the limit() method after where(), for example: .where({ location: _.geoNear({ ... }) }).limit(10).get()

Considerations for Index Usage

Index Quantity Limitations

Important Notice

It is recommended that the number of indexes in a single collection does not exceed 20. Having too many indexes not only fails to improve performance but also brings serious negative impacts.

Negative Impacts of Excessive Indexes:

1. Significant Write Performance Degradation - Every insert or update operation needs to synchronously maintain all indexes, leading to increased write operation time
2. Increased Storage Space and Memory Pressure - Each index consumes additional storage space and memory resources
3. Reduced Query Performance - When the working set exceeds RAM, frequent disk swapping occurs, causing a significant drop in query performance

Index Optimization Recommendations:

• Regularly Review Index Usage - Check the hit frequency of each index on the Index Management page of Cloud Development Platform/Database
• Remove Low-Frequency Indexes - Promptly delete indexes that are rarely or never used to free up resources
• Prioritize High-Frequency Indexes - Retain core indexes that significantly improve query performance
• Use Composite Indexes Wisely - A well-designed composite index can replace multiple single-field indexes

Uniqueness Constraint

When creating an index, select Unique for the index property to add a uniqueness constraint. This constraint requires that values corresponding to the indexed field must be unique within the collection.

For example, if a collection has an index field foo created with the "unique" attribute, then no documents with identical foo field values can exist within that collection.

⚠️ Note: If a field does not exist in a record, its value defaults to null for the indexed field. If the index has a uniqueness constraint, no two or more records are allowed to have a null value for this field / lack this field.

Size Limits

• The size of an indexed field is limited to 1024 bytes
• When adding an index, if any document in the collection has an indexed field exceeding 1024 bytes, an error will be reported during index creation.
• For a field with an index, inserting a document where this field exceeds 1024 bytes will result in an error.

💡 Note: Each English letter (regardless of case) occupies 1 byte of space, while each Chinese character occupies 2 bytes of space.

Regular Expression Query

⚠️ Note: Regular expression queries cannot utilize indexes to enhance performance. It is recommended to use full-text indexes instead when necessary.