mongodb 'count' with query is very slow

Question

everyone,im use a mongodb 2.4.6 version and in windows 2008 64-bit.

i have a collection that have two million records and need to search and paging in client.

db.products.find({"catalogs":1205}).skip().limit() is very fast .

but when calculate total record count:

db.products.find({"catalogs":1205},{"_id":1}).count() is too slow.

>> 442312 records.

>>[log] Sat Sep 28 00:20:01.566 [conn10] command products.$cmd command: { count: "products", query: { catalogs: 1205.0 }, fields: { _id: 1.0 } } ntoreturn:1 keyUpdates:0 locks(micros) r:460681 reslen:48 460ms

this count command elapsed time is 460ms,is too slow.if we have a lot of request that very terrible.

i created a index for a 'catalogs' field and can't use $inc command because query could be very complex.

im googling some like this problem and found this 'count' performance bug already fixed in mongodb 2.4 version.

from http://docs.mongodb.org/manual/release-notes/2.4-overview/

Improvements to count provide dramatically faster count operations. Counting is now up to 20 times faster for low cardinality index based counts.

what ways can improve count?thanks.

update some information

> db.products.getIndexes()
[
    {
            "v" : 1,
            "key" : {
                    "_id" : 1
            },
            "ns" : "products.products",
            "name" : "_id_"
    },
    {
            "v" : 1,
            "key" : {
                    "catalogs" : 1,
                    "created" : -1
            },
            "ns" : "products.products",
            "name" : "catalogs_1_created_-1"
    }
]

the query and elapsed time:

>db.products.find({"catalogs":1205},{"_id":1}).limit(20)
>>Tue Oct 01 15:39:19.160 [conn2] query products.products query: { catalogs: 1205.0 } cursorid:277334670708253 ntoreturn:20 ntoskip:0 nscanned:21 keyUpdates:0 locks(micros) W:5045 r:1017 nreturned:20 reslen:704 1ms

the query exaplin:

>db.products.find({"catalogs":1205},{"_id":1}).explain()

{
    "cursor" : "BtreeCursor catalogs_1_created_-1",
    "isMultiKey" : true,
    "n" : 451466,
    "nscannedObjects" : 451466,
    "nscanned" : 451466,
    "nscannedObjectsAllPlans" : 451466,
    "nscannedAllPlans" : 451466,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2,
    "nChunkSkips" : 0,
    "millis" : 2969,
    "indexBounds" : {
            "catalogs" : [
                    [
                            1205,
                            1205
                    ]
            ],
            "created" : [
                    [
                            {
                                    "$maxElement" : 1
                            },
                            {
                                    "$minElement" : 1
                            }
                    ]
            ]
    },
    "server" : "WIN-O47CO6C2WXY:27017"

}

Answer 1

The reason this count query is not particularly fast is because it has to scan 451466 entries in the index to count up entries. In other words, your query is not very selective relative to the index and size of the entries that satisfy the query.