MongoDB 'count ()' is very slow. How do we improve / work with him?

Question

I am currently using MongoDB with millions of data records. I found one thing that is very annoying.

When I use the count () function with a small amount of requested data, it is very fast. However, when the requested data collection contains thousands or even millions of data records, the whole system becomes very slow.

I made sure to index the required fields.

Has anyone come across the same thing? How will you do to improve this?

Answer 1

Now there is one more optimization than creating the right index.

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

If you need some kind of counters, I suggest that you pre-calculate them when possible. Using atomic $ inc , and don't use count({}) at all.

But the MongoDB guys are working hard on MongoDB, therefore, they plan to count({}) improvements in MongoDB 2.1 according to the JIRA error .

Answer 2

You can verify that the index is actually used without access to the disk.

Let's say you want to count entries with the name: "Andrew"

You guarantee the index by name (how you did it) and

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

you can verify that this is the fastest way to count (with the exception of precalculation) by checking that

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

displays the index_only field set to true.

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

Answer 3

At the moment, you are very unlucky to consider mongodb terrible and will not improve in the near future. See: https://jira.mongodb.org/browse/SERVER-1752

From experience, you will almost never use it if it is a one-time thing that happens very rarely, or your database is quite small.

As @Andrew Orsich said, use counters whenever possible (falling on counters is a global write lock, but better than count () independently).

Answer 4

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

 db.Account.createIndex( { "date_checked_1": 1 }, { sparse: true } ) db.Account.find({ "dateChecked" : { $exists : true } }).count() 

318 thousand entries in the collection

• 0.31 s - with sparse index
• 0.79 s - with unsharp index