Index Recommendation
Last updated: 2025-07-09 14:52:26
Index optimization is an important part of database optimization. An optimal index can improve the query efficiency of the entire database. In view of the Ops characteristics of TencentDB for MongoDB, DBbrain offers the index recommendation feature to help you easily increase the global indexing efficiency of your instance.
After collecting and automatically analyzing slow logs in real time, the index recommendation feature proposes globally optimal indexes and rank them by their impact on the performance. An index that has a greater recommendation value will increase the performance more significantly. In addition, this feature also displays the slow queries and performance metrics associated with the recommended indexes, as well as invalid and duplicate indexes and their causes.
You only need to perform one operation based on the recommended indexes, and you can easily check the operation progress.
Unlocking Index Recommendation Feature
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. Read the data privacy risks and features as shown below. Check the Agree to use option and click Enable Now.
Note:
When you unlock the index recommendation feature for the first time, all data may not be obtained immediately as the calculation starts from the current time point. After this feature is enabled for a while, more data information will be displayed.
After the index recommendation feature is unlocked, the database performance is basically unaffected, and you can use it confidently. (For large tables with hundreds of millions of data processed daily, with a database specification of 4-core 8 GB, the index recommendation feature sampling period lasts 10 minutes, consuming approximately 0.3 CPUs).
Viewing Recommended Index Information</0>
After unlocking the index recommendation feature, you can view the recommended index information.
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. View recommended index information.
View overall instance optimization level and optimization statistics information.
Optimization level: It provides four optimization levels (S > A > B > C). Level S indicates the optimal database performance, while level C indicates the worst performance, requiring urgent optimization.
Recommended index Sets: The number of recommended index collections.
Recommended indexes: The number of recommended indexes.
Optimization Historical : the number of historical index optimizations for the current instance. Click View Details to view specific historical index optimization information.
Optimized Slow Queries: The sum of slow queries generated by the index before adding the index through the index recommendation operation.
View the recommended collection list.
DBbrain aggregates the recommendations based on the detected index data and sorts the indexes by recommendation value. A greater value indicates that the index set contains indexes that require urgent optimization, and their optimization will significantly improve the database performance.
Click the name of an index set, and the recommendation details of indexes in it will be displayed on the right.
Recommended Indexes: It displays the indexes that need to be added due to excessively slow queries. Similarly, they are sorted by recommendation value, and a greater value indicates a more significant performance improvement after the indexes are added.
Existing Indexes (invalid): It displays indexes that are suggested for deletion due to being invalid.
Operation Records: You can view the history of index additions or deletions for the set. Operations on ongoing indexes can also be terminated.
Adding Indexes Based on Recommendations
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. On the Recommended Indexes tab, click different index information, and the corresponding slow query analysis and records will be displayed on the right.
5. Click the icon in the red box as shown below to zoom in the slow query window for clearer information display. You can also download the slow query information.
6. In the Auto-Generate Execution Statement module, select the generation method (including the Default and the Specify Options). DBbrain will automatically generate the execution statements based on your selection. After confirming, you can click Create Index.
7. To perform index operations, you need to log in to the database and validate permissions first.
8. You can view the index creation progress. You can also view the index set's operations in its Operation Records.
9. In the operation list, you can view the historical addition or deletion details of indexes in the index set and terminate the indexes being processed.
Note：
To ensure the stability of your production database, when an index in the index set is being created or deleted, you cannot add or delete another index in the index set, and the system will report an error if you do so.
Deleting Invalid Indexes Based on Recommendations
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. On the Invalid tab, view and delete invalid indexes. When your database contains an invalid index, the index recommendation system will display the reason for its invalidity and generate a deletion command. You can delete the index as prompted.
Viewing Index History and Index Adding Effect
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. On the right of the recommended collection list, click History, or under optimization statistics, click View Details. You can view the historical index optimization information of the overall index operations of the current instance.
5. In the Operation column, click Compare to view the effect before and after optimization.
Disabling Index Recommendation Feature
1. Log in to the DBbrain Console.
2. In the left sidebar, choose Performance Optimization.
3. Select MongoDB database type and instance ID at the top of the page, and select the Index Recommendation tab.
4. In the upper right corner of the page, click Disable Service, and in the pop-up dialog box, click OK.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No
Feedback