CODEWITHSUNDEEP
mongodbexceptionsortingindexing
assaf_miz84
assaf_miz84

Reputation: 687

Mongo DB sorting exception - too much data for sort() with no index

Using MongoDB version 2.4.4, I have a profile collection containing profiles documents. I have the following query:

Query: { "loc" : { "$near" : [ 32.08290052711715 , 34.80888522811172] , "$maxDistance" : 0.0089992800575954}} 
Fields: { "friendsCount" : 1 , "tappsCount" : 1 , "imageUrl" : 1 , "likesCount" : 1 , "lastActiveTime" : 1 , "smallImageUrl" : 1 , "loc" : 1 , "pid" : 1 , "firstName" : 1} 
Sort: { "lastActiveTime" : -1}

Limited to 100 documents.

loc - embedded document containing the keys ( lat,lon)

I am getting the exception:

org.springframework.data.mongodb.UncategorizedMongoDbException: too much data for sort() with no index. add an index or specify a smaller limit;

As stated in the exception when I down-size the limit to 50 it works.. but it ain't option for me.
I have the following 2 relevant indexes on the profile document:

{'loc':'2d'}
{'lastActiveTime':-1}

I have also tried compound index as below but without success. 

{'loc':'2d', 'lastActiveTime':-1}

This is example document (with the relevant keys):

{
  "_id" : "5d5085601208aa918bea3c1ede31374d",
  "gender" : "female",
  "isCreated" : true,
  "lastActiveTime" : ISODate("2013-04-08T11:30:56.615Z"),
  "loc" : {
    "lat" : 32.082230499955806,
    "lon" : 34.813542940344945,
    "locTime" : NumberLong(0)
  }
}

There are other fields in the profile documents .. basically average profile document size is 0.5 MB
correct me if I am wrong but if I am specifying only the relevant response fields (as I do)
it is not the cause for the problem.

Don't know if it helps but when I down-size the limit size to 50 and the query succeed
I have the following explain information (via MongoVUE client)

 cursor : GeoSearchCursor
 isMultyKey : False
 n : 50
 nscannedObjects : 50
 nscanned : 50
 nscannedObjectsAllPlans : 50
 nscannedAllPlans : 50
 scanAndOrder : True
 indexOnly : False
 nYields : 0
 nChunkSkips : 0
 millis : 10
 indexBounds :


It is a blocker for me and I will appreciate your help, what am I doing wrong? How can I make the query roll with the needed limit size?

Upvotes: 3

Views: 3365

Answers (1)

AntonioOtero
AntonioOtero

Reputation: 1789

Try creating a compound index instead of two indexes. 

db.collection.ensureIndex( { 'loc':'2d','lastActiveTime':-1 } )

You can also suggest the query which index to use:

db.collection.find(...).hint('myIndexName')

Upvotes: 1

Related Questions