Index Management

Indexes are a key technology for improving database query performance. By creating indexes for commonly queried fields, it can significantly enhance query speed and user experience.

📋 Management Portal

1. Access the console: Go to the CloudBase console
2. Select database: Switch to Database/Document-based
3. Enter Index Management: Select the target collection and click the Index Management tab
4. Manage indexes: Create, delete, or modify index configurations

🎯 Detailed Index Types

📌 Single Field Index

Applicable Scenarios: Query and sort operations targeting a single field

Feature Description:

• Supports nested field indexing, accessed using "dot notation"
• Can specify 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

💡 Tip: Use dot notation for nested fields. For example, style.color represents accessing the color field within the style object.

🔗 Composite Index

Applicable Scenarios: Composite queries and complex sorting operations across multiple fields

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 Rules:

Assume creating 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})

💡 Principle of Prefix Matching

The prefixes of the composite index (name, age, score) include:

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

Key Features of Composite Indexes

🔄 1. Importance of Field Order

The order of index fields directly impacts query performance:

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

📊 2. Impact of Sort Order

In sort queries, the sort direction of the index affects whether it can be utilized:

Index configuration: age: asc, score: desc

Query Sort Order	Index Hit	Explanation
age: asc, score: desc	✅ Hit	Exactly matches the index order
age: desc, score: asc	✅ Hit	Index can be used in reverse order
age: asc, score: asc	❌ Miss	Inconsistent sort order
age: desc, score: desc	❌ Miss	Inconsistent sort order
score: any, age: any	❌ Miss	Does not satisfy the prefix rule

Index configuration: age: asc, score: asc

Query Sort Order	Index Hit	Explanation
age: asc, score: asc	✅ Hit	Exactly matches the index order
age: desc, score: desc	✅ Hit	Index can be used in reverse order
age: asc, score: desc	❌ Miss	Inconsistent sort order
age: desc, score: asc	❌ Miss	Inconsistent sort order

📍 Geolocation Index

Use cases: Geolocation queries, nearby search, region filtering

Features:

• Supports planar geometry for geolocation indexes
• A dedicated geolocation index must be created for geolocation fields.
• Supports geometric shapes such as points, lines, and polygons

Data Structure Example:

{
  "_id": "store_001",
  "name": "Starbucks Coffee Shop",
  "location": {
    "type": "Point",
    "coordinates": [116.4074, 39.9042]  // [longitude, latitude]
  },
  "address": "Chaoyang District, Beijing"
}

Index Creation:

1. Select the collection in the console
2. Go to the Index Management page
3. Select the geolocation field (e.g., location)
4. Set the index type to "geolocation index"

Query Example:

// Query stores within 1000 meters
db.collection('stores').where({
    location: db.command.geoNear({
        geometry: db.Geo.Point(116.4074, 39.9042),
        maxDistance: 1000
    })
}).get()

Notes on Index Usage

Uniqueness Constraint

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

For example, if an index field foo is created in a collection with the "Unique" attribute, then no two documents in this collection can have the same value for the foo field.

Note

It is particularly important to note that if a field does not exist in a record, then for the index field, its value defaults to null. If the index has a uniqueness constraint, then having two or more records with this field being empty / missing is not allowed.

Size Limitations

• The index field size limit cannot exceed 1024 bytes.
• When adding an index, if any existing document in the collection has an index field exceeding 1024 bytes, the index creation will fail with an error.
• For a field that already has an index set, inserting a document where this field exceeds 1024 bytes will throw an error.

Note

Each English letter (case-insensitive) occupies one byte of space, while each Chinese character occupies two bytes of space.

Regular Expression

Regular expression queries cannot use indexes to improve performance.