MongoDB 'count()' is very slow. How do we

问题:

I am currently using MongoDB with millions of data records. I discovered one thing that's pretty annoying.

When I use 'count()' function with a small number of queried data collection, it's very fast. However, when the queried data collection contains thousand or even millions of data records, the entire system becomes very slow.

I made sure that I have indexed the required fields.

Has anybody encountered an identical thing? How do you do to improve that?

回答1:

There is now another optimization than create proper index.

db.users.ensureIndex({name:1});
db.users.find({name:"Andrei"}).count();

If you need some counters i suggest to precalculate them whenever it possible. By using atomic $inc operation and not use count({}) at all.

But mongodb guys working hard on mongodb, so, count({}) improvements they are planning in mongodb 2.1 according to jira bug.

回答2:

You can ensure that the index is really used without any disk access.

Let's say you want to count records with name : "Andrei"

You ensure index on name (as you've done) and

db.users.find({name:"andrei"}, {_id:0, name:1}).count()

you can check that it is the fastest way to count (except with precomputing) by checking if

db.users.find({name:"andrei"}, {_id:0, name:1}).explain() 

displays a index_only field set to true.

This trick will ensure that your query will retrieve records only from ram (index) and not from disk.

回答3:

You are pretty much out of luck for now, count in mongodb is awful and won't be getting better in the near future. See: https://jira.mongodb.org/browse/SERVER-1752

From experience, you should pretty much never use it unless it's a one time thing, something that occurs very rarely, or your database is pretty small.

As @Andrew Orsich stated, use counters whenever possible (the downfall to counters is the global write lock, but better than count() regardless).

回答4:

For me the solution was change index to sparse. It depend on specific situation, just give it a try if you can.

db.Account.createIndex( { "date_checked_1": 1 }, { sparse: true } )

db.Account.find({    
     "dateChecked" : { $exists : true }    
}).count()

318 thousands records in collection

• 0.31 sec - with sparse index
• 0.79 sec - with non-sparse index