Use Indexes ​

By creating indexes, the database can directly retrieve the corresponding fields when querying, thereby significantly improving query efficiency. If there is no index, even the simplest query needs to scan every record in the table, and when the data size is huge, the operation will fail because of triggering slow query.

Create Indexes ​

In the "Database" area of the console, select the corresponding table, and switch to the "Indexes" page, you can see all the indexes of the current table.

Click the Add Index button on the right, and enter the index information in the pop-up window.

Index Field (required)

Select or enter the fields to index. Only the first-level field will be displayed in the drop-down box. If you want to index the subfield, please enter it yourself. For example, info.location means to index the info's subfield location.

Index Type (required)

Each field needs to be set with the index type, including the following types:

• ASC: ascending index, that is, from small to large
• DESC: descending index, that is, from large to small
• 2DSPHERE: geospatial location index, refer to Geo-based Query

Name (optional)

Set a name for the index, if not, it will be automatically generated by the system.

Advanced Options (optional)

• UNIQUE: Create a unique index, refer to Unique Indexes

Click the "Create" button in the pop-up window to complete the creation. After success, you can see the index you just created appears in the list. At this time, all related query operations will be optimized because of the index.

Single Field Indexes ​

The ASC/DESC index created on a single field is called "Single Field Index", such as the system default { _id: 'ASC' } index. After a single index is crated, queries and sorting operations on this field will be optimized.

Suppose we have a table PersonsTable that stores user information, including a age field. When the { age: 'ASC' } index is established, query conditions similar to the following will be optimized:

// Equal conditions
PersonsTable.where({ age: 20 }).find();
// Comparison
PersonsTable.where({ age: db.gt(30) }).find();
// Sort
PersonsTable.where().sort({ age: 1 }).find();

// Equal conditions
PersonsTable.where({ age: 20 }).find();
// Comparison
PersonsTable.where({ age: db.gt(30) }).find();
// Sort
PersonsTable.where().sort({ age: 1 }).find();

Compound Indexes ​

Indexes created by combining multiple fields are called "Compound Indexes". Compound indexes are generally used to optimize query operations with multiple conditions, or combined sorting of multiple fields.

For example, there is a table called PersonsTable, which contains two fields name and age. When the index of { name: 'ASC', age: 'ASC' } is established, query conditions similar to the following will be optimized:

// Equal conditions
PersonsTable.where({ name: 'Micheal', age: 20 }).find();
// Comparison
PersonsTable.where({ name: 'Micheal', age: db.gt(30) }).find();
// Sort
PersonsTable.where().sort({ name: 1, age: 1 }).find();

// Equal conditions
PersonsTable.where({ name: 'Micheal', age: 20 }).find();
// Comparison
PersonsTable.where({ name: 'Micheal', age: db.gt(30) }).find();
// Sort
PersonsTable.where().sort({ name: 1, age: 1 }).find();

Sorting Order ​

Compound indexes are very sensitive to the order of fields, and the order of fields in sorting must be exactly the same as the order of fields in the index. For example, for index { name: 'ASC', age: 'ASC' }:

• Can optimize sort({ name: 1, age: 1 })
• Cannot optimize sort({ age: 1, name: 1 })

In addition, whether the index is in ascending or descending order will also affect whether it can be optimized. For example, for index { name: 'ASC', age: 'DESC' }:

• Can optimize sort({ name: 1, age: -1 }) and sort({ name: -1, age: 1})
• cannot optimize sort({ name: 1, age: 1}) and sort({ name: -1, age: -1 })

Prefix Rules ​

Compound indexes can be used by intercepting prefixes. For example, compound index { name: 'ASC', age: 'ASC', location: 'ASC' } can be used as the following 3 indexes:

• { name: 'ASC' }, which can optimize the query for the name field
• { name: 'ASC', age: 'ASC' }, which can optimize the combined query for name and age fields
• { name: 'ASC', age: 'ASC', location: 'ASC' }, which can optimize the combined query for the three fields of name, age and location

The prefix interception rules start from the left, that is, the above index cannot be used as { age: 'ASC', location: 'ASC' } index.

Unique Indexes ​

"Unique Indexes" can ensure that the value of the indexed field is unique in the entire table, and any insertion or update that causes duplicate values will throw an exception. For example, the index created by the system for the _id field by default is a unique index.

Expand the advanced options in the dialog box of creating an index and check "UNIQUE" to create a unique index.

Unique Indexes on Single Field ​

For a unique index of { name: 'ASC' }, the following operations will return an error because of breaking the uniqueness:

• The table contains a record with name as 'Micheal', insert another record with name as 'Micheal'
• The table contains a record whose name is 'Micheal', update another record to change the name to 'Micheal'
• The table contains a record without a name field, and then inserts a record without a name field, two null values will also cause the destruction of uniqueness

Unique Compound Indexes ​

The unique index can also be used on Compound Indexes, then the database will ensure that the combination of these multiple field values is unique.

For example, for a unique compound index of { name: 'ASC', age: 'ASC' }, two records with same name and age field values will fail. However, two records like { name: 'Micheal', age: 20 } and { name: 'Micheal', age: 30 } can exist at the same time because the age field has different values.

Index Optimization for Regex ​

When using a regular expression to query a field, only when the expression starts with ^ can use the index to optimize.

For example, for an index on { name: 'ASC' }, the following query can be optimized:

• PersonsTable.where({ name: /^M/ }).find(), that is, query all records starting with 'M'

the following query cannot be optimized:

• PersonsTable.where({ name: /foo/ }).find(), that is, to query all records containing 'foo'
• PersonsTable.where({ name: /bar$/ }).find(), that is, query all records ending with 'bar'