CODEWITHSUNDEEP
javaspringmongodbspring-mvc
Nabor
Nabor

Reputation: 1701

MongoDB find has the same speed with and without index

I use MongoTemplate from Spring to access a MongoDB.

final Query query = new Query(Criteria.where("_id").exists(true));
query.with(new Sort(Direction.ASC, "FIRSTNAME", "LASTNAME", "EMAIL"));
if (count > 0) {
    query.limit(count);
}
query.skip(start);
query.fields().include("FIRSTNAME");
query.fields().include("LASTNAME");
query.fields().include("EMAIL");
return mongoTemplate.find(query, User.class, "users");

I generated 400.000 records in my MongoDB. When asking for the first 25 Users without using the above written sort line, I get the result within less then 50 milliseconds.

With sort it lasts over 4 seconds.

I then created indexes for FIRSTNAME, LASTNAME, EMAIL. Single indexes, not combined ones

mongoTemplate.indexOps("users").ensureIndex(new Index("FIRSTNAME", Order.ASCENDING));
mongoTemplate.indexOps("users").ensureIndex(new Index("LASTNAME", Order.ASCENDING));
mongoTemplate.indexOps("users").ensureIndex(new Index("EMAIL", Order.ASCENDING));

After creating these indexes the query again lasts over 4 seconds.

What was my mistake?

-- edit MongoDB writes this on the console...

Thu Jul 04 10:10:11.442 [conn50] query mydb.users query: { query: { _id: { $exists: true } }, orderby: { LASTNAME: 1, FIRSTNAME: 1, EMAIL: 1 } } ntoreturn:25 ntoskip:0 nscanned:382424 scanAndOrder:1 keyUpdates:0 numYields: 2 locks(micros) r:6903475 nreturned:25 reslen:3669 4097ms

Upvotes: 0

Views: 1466

Answers (2)

ritesh
ritesh

Reputation: 917

Thu Jul 04 10:10:11.442 [conn50] query mydb.users query: 
{ query: { _id: { $exists: true } }, orderby: { LASTNAME: 1, FIRSTNAME: 1, EMAIL: 1 } } 
ntoreturn:25 ntoskip:0 nscanned:382424 scanAndOrder:1 keyUpdates:0 numYields: 2 
locks(micros) r:6903475 nreturned:25 reslen:3669 4097ms

Possible bad signs:

Your scanAndOrder is coming true (scanAndOrder=1), correct me if I am wrong.

It has to return (ntoreturn:25) means 25 documents but it is scanning (nscanned:382424) 382424 documents.

indexed queries, nscanned is the number of index keys in the range that Mongo scanned, and nscannedObjects is the number of documents it looked at to get to the final result. nscannedObjects includes at least all the documents returned, even if Mongo could tell just by looking at the index that the document was definitely a match. Thus, you can see that nscanned >= nscannedObjects >= n always.

Context of Question:

Case 1: When asking for the first 25 Users without using the above written sort line, I get the result within less then 50 milliseconds.

Case 2: With sort it lasts over 4 seconds.

query.with(new Sort(Direction.ASC, "FIRSTNAME", "LASTNAME", "EMAIL"));

As in this case there is no index: so it is doing as mentioned here:

This means MongoDB had to batch up all the results in memory, sort them, and then return them. Infelicities abound. First, it costs RAM and CPU on the server. Also, instead of streaming my results in batches, Mongo just dumps them all onto the network at once, taxing the RAM on my app servers. And finally, Mongo enforces a 32MB limit on data it will sort in memory.

Case 3: created indexes for FIRSTNAME, LASTNAME, EMAIL. Single indexes, not combined ones

I guess it is still not fetching data from index. You have to tune your indexes according to Sorting order

Sort Fields (ascending / descending only matters if there are multiple sort fields)

Add sort fields to the index in the same order and direction as your query's sort

For more details, check this http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/

Possible Answer:

In the query orderby: { LASTNAME: 1, FIRSTNAME: 1, EMAIL: 1 } } order of sort is different than the order you have specified in :

mongoTemplate.indexOps("users").ensureIndex(new Index("FIRSTNAME", Order.ASCENDING));
mongoTemplate.indexOps("users").ensureIndex(new Index("LASTNAME", Order.ASCENDING));
mongoTemplate.indexOps("users").ensureIndex(new Index("EMAIL", Order.ASCENDING));

I guess Spring API might not be retaining order: https://jira.springsource.org/browse/DATAMONGO-177

When I try to sort on multiple fields the order of the fields is not maintained. The Sort class is using a HashMap instead of a LinkedHashMap so the order they are returned is not guaranteed.

Could you mention spring jar version?

Hope this answers your question.

Correct me where you feel I might be wrong, as I am little rusty.

Upvotes: 0

obecker
obecker

Reputation: 2377

You have to create a compound index for FIRSTNAME, LASTNAME, and EMAIL, in this order and all of them using ascending order.

Upvotes: 1

Related Questions