Question 290611 in Mongodb Question posted in
The official documentation can be found here.

Why does my MongoDB query use in-memory sort with a compound text index?

I am trying to query on MongoDB with a compound text index as below:

index : {name : 'text' , createdAt:'-1'}

That will create a compound index for the name and createdAt field, but when I try to query on those fields, it is using in-memory sorting although i defined the sorting order in a compound index.

db.table.find({$text:{$search:"text"}}).sort({createdAt : -1})

I checked MongoDB documentation, but I did not find any use case that matched with mine. I am expecting to remove in-memory sorting with text search with the above compound index.

Tags:

2

Answers


  1. 0

    In MongoDB, when you use a compound index that includes a text index field along with another field, the sort order specified in the compound index is not directly used for sorting when performing a text search. Text search queries have their own internal sorting requirements, and MongoDB may resort to in-memory sorting if the query conditions necessitate it.

    In your case, you have a compound text index on { name: ‘text’, createdAt: -1 }. However, when you perform a text search query using $text and sort by createdAt, MongoDB might resort to in-memory sorting if the text search score or other conditions are affecting the sort order.

    To optimize queries and reduce the likelihood of in-memory sorting, you can consider the following:

    Instead of a compound index, you could create separate indexes for the text search and the sorting field. For example:

    db.table.createIndex({ name: 'text' });
db.table.createIndex({ createdAt: -1 });

    Or you can use the projection parameter to limit the fields returned to only those needed. This might help in certain cases:

    db.table.find(
  { $text: { $search: "text" } },
  { _id: 0, name: 1, createdAt: 1 }
).sort({ createdAt: -1 });
    Login or Signup to reply.
  2. 0

    From the MongoDB documentation (https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text):

    Compound Text Indexes

    For a compound index that includes a text index
    key along with keys of other types, only the text index field
    determines whether the index references a document. The other keys do
    not determine whether the index references the documents.

    Checking the explain(true) reveals that only the text component of the index is used. Trying to be clever by switching the order of the compound index to:

    db.foo.createIndex({createdAt:-1,name: "text"});

    and issuing:

    db.foo.find( { "createdAt":{$gt:0}, $text: { $search: "coffee" } } ).sort({createdAt:-1}).explain(true);

    yields this error because a createdAt equality is required, not a range operator ($gt).

    Proj: {}
 planner returned error :: caused by :: failed to use text index to satisfy $text query (if text index is compound, are equality predicates given for all prefix fields?)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search