Performance of count()

[Klaus]

After doing some improvements on my indexes I'm now stuck on the
performance of the count operation. Our application shall let the user
search through some documents, filter them, page through them. Paging
works like a charm and is super fast but count() seems to be too slow
especially for huge results - seems that it costs linear depending on
the number of results.

I have a db with about 500,000 documents, for testing I did an index
on one field that I want to filter ('price').

What I want to show to the user is something like: "32.232 results
found, you are viewing page 2 with 50 entries"

db.Docs.find({price:{$lte:1000}}).sort({price:1}).limit(50)

returns immediately (< 3ms) but if I do that a count and the number of
results if very big (approx. 200.000 entries), then it takes up to
200ms on my notebook

db.Docs.find({price:{$lte:1000}}).count()

this would be ok if I only had one user but I'm afraid that won't
scale with a huge amount of users.

I'm not sure if there's any way to speed up count() calculation on
server side. I already did the thing that is mentioned in the docs
(use an index on the field that you use for counting()). Maybe I'm
also expecting too much from mongo, but 200ms on my notebook to get
the count only seems a bit too much for me.

The only thing that came up in my mind is to do some "count()-caching"
in my application, that is, if the count is over a reasonable number
(e.g. 1000 entries) then I cache the count for that query in my
application for some while and return that value (even if that might
be slightly wrong then as it's irrelevant to the user if there are
1534 or 1578 results).

Any other ideas?

[Alvin Richards]

So there could be many things going on here. First check that you are
really using an index, by using the "explain" feature

db.Docs.find({price:{$lte:1000}}).count().explain()

If you see "BasicCursor" then its doing a full table scan.

Next as the query executes, check
-- mongostat
-- iostat -x 2

This will give a picture of what mongo is doing and the i/o load on
the system.

If you want to post the results here, we can take a look.

-Alvin

[Klaus]

Well, explain() is not available on count(), but if I do
db.Docs.find({price:{$lte:1000}}).explain() then I can see that the
BtreeCursor is used, so that should be fine

mongostat doesn't show any numbers except zeros, even if I run the
count() query multiple times (and I ensured that mongostat is
connected). Anyways, if I put the profilingLevel to 2 on the db, I can
see those queries over there:

Tue Oct 05 14:04:59 [conn1] query spm.$cmd ntoreturn:1 command:
{ count: "Offer", query: { catlft: { $lte: 150000.0 } }, fields:
{ title: 1.0 } } reslen:64 bytes:48 442ms

don't know what's going on here.

[Klaus]

ah yes, and iostat is not option for me as I'm developing on a win7 64
bit machine. and I don't think that's an issue of I/O here, everything
should be in RAM (I have 4 gigs)

[mathias]

count() without a query is an instant O(1) operation.

count({}) with a query is an O(n) operation. Having an index on the
query field will speed up the process, but it will still require
counting each object. I would suggest keeping real-time counters using
$inc where possible to avoid large counts.