Index Recommendation

Scenarios

The index recommendation feature analyzes slow query logs of instances to automatically identify inefficient queries. Based on query patterns, column distribution, and other factors, it provides index recommendations that can improve query performance. This feature helps you quickly pinpoint missing or suboptimal indexes, reduces the complexity of database tuning, and enhances query efficiency.

Precautions

• The recommended results are for reference only and are not guaranteed to be fully accurate.

  Index recommendations are generated based on historical query samples from slow query logs and therefore have the following limitations:

  • Slow query log samples may not cover all query scenarios and the recommendations may be incomplete.
  • Whether a recommended index is truly effective depends on your actual business logic, data distribution, and collection size.
  • In some cases, the recommended index may be redundant or overlap with existing indexes, so you need to review your current index list before creating new ones.
  • If possible, verify the recommended indexes in a test environment before applying them to production.
• Create indexes during off-peak hours.

  Even when you use the background: true option to build the index in the background, index creation still consumes system resources.

  • The index creation process uses CPUs, memory, and disk I/Os.
  • For collections with large data volumes, index creation may take a long time.
  • Running index build operations during peak hours of high concurrency may increase query latency and affect user experience.
• Avoid creating too many indexes at a time.

  Having more indexes does not necessarily lead to better performance. Excessive indexes increase the overhead of write operations because each write must update all relevant indexes. You are advised to create only the one or two necessary indexes at a time and observe the impact before deciding whether to add more. For outdated indexes that are no longer used, remove them to keep the index set compact.

• Check existing indexes before creating new ones.

  Before creating recommended indexes, check the existing indexes on the collection to avoid duplicates. You can run the following command:

  db.<collectionName>.getIndexes()

How It Works

The core workflow of index recommendation is as follows:

1. Slow query log collection

   It continuously collects slow query logs whose execution time exceeds the configured threshold. The logs record key information such as query statements, execution plans, and the number of scanned rows.

2. Query pattern analysis

   It analyzes the query statements in the slow query logs to extract high-frequency query patterns and identify dimensions such as filter fields, sort fields, and projection fields.

3. Indexing strategy generation

   It automatically generates index recommendations based on the query patterns and MongoDB index design best practices (including the leftmost-prefix rule and field ordering for compound indexes).

4. Recommendation result output

   It presents the recommended indexes as readable index definitions, which you can directly reference and execute in the console or MongoDB Shell.

Procedure

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Databases > Document Database Service.
4. On the Instances page, click the target instance name.
5. In the navigation pane, choose Slow Query Logs.
6. On the Log Details tab page, you can find index recommendations in the displayed table.
   Figure 1 Index recommendations
   

Follow-up Operations

Index recommendation serves as an auxiliary tool for database performance tuning and can significantly reduce the cost of manually analyzing slow query logs. To achieve the best optimization results, it is essential to review the recommendations carefully and make decisions based on your actual workload. If you have any questions, submit a service ticket.

Helpful Links

Working with Indexes