Positional $ operator vs ElemMatch; Performance

Working with Data
1

hello i have simple collection

{
    userId: 1,
    books: [
         { bookId: 55, otherfields },
         { bookId: 66, otherfields },
    ]
}

and queries which get the book :

col.findOne({_id: 1, 'books.bookId': 55}, { projection: { _id:0, 'books.$':1} });

col.findOne({_id: 1, 'books.bookId': 55}, { projection: { _id:0, 'books': { $elemMatch : { bookId: 55 } }} });

Two question

  1. what is different in my example?
  2. why is the performance 2 times different ?
2

Hi @alexov_inbox ,

I am not sure I understand the query, are you searching on _id or userId?

Can you share the indexes on the collection and the explain(true) output for both queries?

I believe that a diffrenence in performance might be a covered query that do not fetch docs vs a simple index to doc fetch.

3

yes userId = _Id (its my mark)
I have only 1 index _id .
But for test i insert 50000 books for user.

I found on the Internet that “under the soot” are two identical queries. But my test shows that there is clearly a difference.

4

First you must index {_id : 1, books.bookId : 1} as you search by those both.

I will need execution statistics to compare you use case.

5

Wait you are inserting 50000 books for one user in a single document?

This is certnaly an antipattern for MongoDB schema design. Why would a user need all of his 50000 books in a single document. Having those huge arrays cause performance issues in both query and writing paths.

Please read the following to find a better schema design:

Thanks
Pavel