I created an index but I still get "too much data for sort() with no index"

737 views
Skip to first unread message

lawrenc...@gmail.com

unread,
Feb 13, 2014, 11:05:45 PM2/13/14
to clojure...@googlegroups.com
I have a query function that looks like this:

(defn paginate-results [item-type which-page]
  {:pre [
         (= (type item-type) java.lang.String)
         (= (type which-page) java.lang.String)
         (= (type (Integer/parseInt which-page)) java.lang.Integer)
         (or (pos? (Integer/parseInt which-page)) (zero? (Integer/parseInt which-page)))
         ]
   :post [(= (type %) clojure.lang.LazySeq)]}
  (with-collection "tma"
    (find { :item-type item-type})
    (fields [:item-name :item-type :user-item-name :created-at])
    (sort (array-map :created-at 1 :user-item-name 1))
    (limit 100)
    (skip (Integer/parseInt which-page))))

At the MongoDb shell command line I do this:

> db.tma.ensureIndex( { "created-at": 1 } );

> db.tma.ensureIndex( { "user-item-name": 1 } );

> db.tma.ensureIndex( { "item-type": 1 } );

That is 1 index for the field used by "find" and also 2 indexes for the fields used by "sort". 

And yet I get: 

com.mongodb.MongoException: too much data for sort() with no index.  add an index or specify a smaller limit

Any suggestions? 

lawrenc...@gmail.com

unread,
Feb 13, 2014, 11:09:06 PM2/13/14
to clojure...@googlegroups.com

And if I change the "limit" to 10, I still get the same error. 

lawrenc...@gmail.com

unread,
Feb 13, 2014, 11:17:50 PM2/13/14
to clojure...@googlegroups.com

Strangely, this function works when which-page is 0 (zero). So if skip() is zero then all is fine. But if skip is 9000, then I get an error. 

lawrenc...@gmail.com

unread,
Feb 13, 2014, 11:21:00 PM2/13/14
to clojure...@googlegroups.com
I look here:


and read:

"The cursor.skip() method is often expensive because it requires the server to walk from the beginning of the collection or index to get the offset or skip position before beginning to return result. As offset (e.g.pageNumber above) increases, cursor.skip() will become slower and more CPU intensive. With larger collections, cursor.skip() may become IO bound."

However, I can do this in the MongoDb shell: 

db.heart.find ({ "item-type" : "users"}).skip (9000);

and that works fine. 

lawrenc...@gmail.com

unread,
Feb 13, 2014, 11:26:09 PM2/13/14
to clojure...@googlegroups.com

Actually, the Clojure code works fine if skip() is as much as 4,600, but anything over that and I get the exception. But from the MongoDb shell, I can say skip(9000) and that works. Why would there be a difference between Monger and what I get at the shell command line? 

Michael Klishin

unread,
Feb 13, 2014, 11:30:58 PM2/13/14
to Monger, a Clojure MongoDB driver

2014-02-14 8:05 GMT+04:00 <lawrenc...@gmail.com>:
com.mongodb.MongoException: too much data for sort() with no index.  add an index or specify a smaller limit

Any suggestions? 

Michael Klishin

unread,
Feb 13, 2014, 11:33:06 PM2/13/14
to Monger, a Clojure MongoDB driver

2014-02-14 8:26 GMT+04:00 <lawrenc...@gmail.com>:
Why would there be a difference between Monger and what I get at the shell command line?

Java client may have differences (e.g. slightly different internal limits) compared to Mongo shell.

Sean Corfield

unread,
Feb 13, 2014, 11:58:59 PM2/13/14
to clojure...@googlegroups.com
Remember it will sort first, then skip. So it will sort enough data for you to then skip 9000 documents and then return the next 100. Sorting enough data to return 9000 documents is going to be the problem.

Also, you seem to have created a separate index for each field in the sort so your sort is only able to use :created-at (if it uses it at all). If you're sorting on type fields, you probably want the index on two fields as well. And then beware of having two applicable indexes - MongoDB will often try multiple applicable indexes and keep the "best" one for quite a while, before it throws away the plan and runs the next query with multiple indexes again.

Sean
signature.asc

lawrenc...@gmail.com

unread,
Feb 14, 2014, 3:01:39 PM2/14/14
to clojure...@googlegroups.com
> Also, you seem to have created a separate index for each field in the sort so your sort is only able to use :created-at (if it 
> uses it at all). If you're sorting on type fields, you probably want the index on two fields as well. And then beware of having 
> two applicable indexes - MongoDB will often try multiple applicable indexes and keep the "best" one for quite a while, before 
> it throws away the plan and runs the next query with multiple indexes again.

Okay, thanks for that, I was not aware of that. If I do this at the MongoDb shell:

db.collection.ensureIndex( { "created-at":1, "user-item-name": 1 } );

and then: 

db.tma.getIndexes();

I get:

[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "tma.tma",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"user-item-name" : 1
},
"ns" : "tma.tma",
"name" : "user-item-name_1"
}
]


It appears the command failed? I do not see a composite index that includes "created-at". 

lawrenc...@gmail.com

unread,
Feb 14, 2014, 3:13:20 PM2/14/14
to clojure...@googlegroups.com

Ah nice! Fixed now and everything works great. Thanks so much to everyone. 
Reply all
Reply to author
Forward
0 new messages