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

Question

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?

You can see in the explain it says 10 millisecond , very fast. — assaf_miz84
– assaf_miz84, Commented Jul 16, 2013 at 15:02
Oh, in the image. Can you get rid of all that whitespace in the image by chance? The relevant portion shows up as 30px or so in SO. Better yet, just copy the data to the question. Also, 10 millis isn't very fast for a high-performance site, that's only 100 requests a second, but maybe that's acceptable for you. — beatgammit
– beatgammit, Commented Jul 16, 2013 at 15:06
Awesome, thanks. I don't know the answer, but hopefully this makes the problem more obvious to someone more familiar with Mongo than I. — beatgammit
– beatgammit, Commented Jul 16, 2013 at 15:24

AntonioOtero · Accepted Answer · 2013-07-16 17:37:08Z

1

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')

answered Jul 16, 2013 at 17:37

AntonioOtero

1,7891 gold badge14 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

assaf_miz84 Over a year ago

already tried the compound index ... as I mentioned in my question. which index to suggest in the hint? I need to indexes to operate the query can I suggest both?

Asya Kamsky Over a year ago

there is only one compound index to suggest.

assaf_miz84 Over a year ago

tried that also creating compound index and suggesting it with hint... without success

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related