What are the Limitations of Indexes in MongoDB?

Limitations of Indexes in MongoDB

Indexes have certain overheads and limits even though they greatly improve query performance. Database design efficiency requires understanding these:

1. Maximum Indexes per Collection:  MongoDB does not allow more than 64 indexes per collection. This limit supports index selection to reduce overhead.
2. Index Name Length: An index name can’t contain more than 125 characters. Because the normal naming method can easily surpass this restriction, this is especially important when establishing indexes on numerous columns. Namespaces database, collection, and index names cannot exceed 123 bytes.
3. Compound Index Field Limit: Compound index fields cannot exceed 31.
4. RAM Usage: In order to provide best performance, indexes are usually stored in RAM. MongoDB may evict portions of your indexes if their overall size exceeds the amount of RAM that is available, which would result in page faults and a decrease in speed when data is read from disc. Make sure your indexes fit within the RAM.
5. Write Overhead: Every index must be changed to reflect data changes, which increases write overhead (inserts, updates, and deletes). Due to this cost, indexes may not be worth it for collections infrequently read.
6. Query Limitations: Regular expressions, negation operators like $nin, $not, and arithmetic operators like $mod prohibit the usage of indexes in queries. A collection scan is usually necessary for $nin. There are restrictions on text indexes as well; for instance, a $text query expression cannot be used with hint().

Advanced

For MongoDB query optimisation and debugging, the explain() command is a vital tool. The query design, the indexes used, and other statistics are all covered in depth.

To use explain(), you append it to your query:

db.collection.find({field: "value"}).explain("executionStats")

With MongoDB 3.0 and later, the “executionStats” mode yields more detailed output, which is particularly helpful.

The explain() result contains important fields such as:

• queryPlanner.winningPlan.stage: This shows the query execution mechanism used by MongoDB.
  • COLLSCAN (Collection Scan): In order for MongoDB to locate the results, it has to do a collection scan, or COLLSCAN. This should be avoided for the majority of queries and is typically ineffective for large collections.
  • IXSCAN (Index Scan): Index Scan, or IXSCAN, is a sign indicating MongoDB utilised an index to answer the query. It indicates that an index was successfully leveraged, which is usually significantly faster, when an IXSCAN is the winning strategy.
• cursor: This field indicates the kind of cursor that is being utilised. In addition to confirming if an index was used, a BtreeCursor provides the name of the index. When there are no indexes, a BasicCursor represents a complete scan.
• nscannedObjects: The total number of disc documents that MongoDB has to go through in order to answer the query is called nscannedObjects.
• nscanned: The total quantity of scanned papers or index entries. The actual number of documents returned, nReturned, should be near nscanned and nscannedObjects for an efficient query.
• totalKeysExamined and totalDocsExamined: In MongoDB 3.0+, totalKeysExamined and totalDocsExamined indicate the number of index entries and documents scanned, comparable to nscanned and nscannedObjects.
• millis / executionTimeMillis: The query’s millisecond execution time. A lower value means higher performance.
• indexOnly: A “covered query” is indicated if this is true. All query criteria and projection fields must be in the same index to cover a query. Since MongoDB can satisfy covered queries from the index without reading the documents, they are efficient.
• scanAndOrder: If this is the case, MongoDB was unable to sort the results using an index and was forced to apply an in-memory sort. Avoid using in-memory sorting for large result sets because they are typically sluggish.

Example: Think about the effects of include a compound index and how explain() represents them. Given a range of near values and a stock_symbol query:

db.values.find({stock_symbol: "GOOG", close: {$gt: 200}})

Higher nscanned values could be displayed by explain() in the absence of a compound index such as {stock_symbol: 1, close: 1}, suggesting inefficiency. Once the compound index has been created:

db.values.createIndex({stock_symbol: 1, close: 1})

The same query’s explain() result would probably display a BtreeCursor on stock_symbol_1_close_1, suggesting an optimum query, with nscanned and nscannedObjects equal to n (documents returned). This demonstrates that query effectiveness is affected by the order of fields in a compound index, particularly when combining range searches with exact matches. The exact match fields should usually be placed first in the compound index for queries involving equality matches and range searches.

For testing, you can also use the hint() method to make MongoDB utilise a particular index. Though it can override the query optimizer’s decision, hint() should be used carefully as it may result in less effective queries.

To optimise your MongoDB queries and guarantee that your database operates effectively, you must be aware of these constraints and become proficient with the explain() tool.