Is MongoDB search without index really slow?

Question

I am trying the performance of MongoDB to compare my current MySQL based solution.

In a collection/table X with three attributes A, B, and C, I have attribute A indexed in both MongoDB and MySQL.

Now I throw 1M data in MongoDB and MySQL, and tries the search performance in this straight-ward scenario.

The insert speed on MongoDB is only 10% faster than insert to MySQL. But that is OK, I knew adopting of MongoDB won't bring a magic promotion of my CRUDs, but I am really surprised by the search in MongoDB without index.

The results shows that, MongoDB select on non-indexed field is ten times slower than the select on a indexed field.

On the other hand, the MySQL select (MyISAM) on non-indexed field is only about 70% slower than the select on a indexed field.

Last but not least, in select with index scenario, MongoDB is about 30% quicker than my MySQL solution.

I wanna know that, is above figures normal? Especially the performance of MongoDB select without index?

I have my code like:

BasicDBObject query = new BasicDBObject("A", value_of_field_A);
DBCursor cursor = currentCollection.find(query);
while(cursor.hasNext()) {
    DBObject obj = cursor.next();
    // do nothing after that, only for testing purpose
}

BTW, from business logic's prespective, my collection could be really large (TB and more), what would you suggest for the size of each physical collection? 10 million Documents or 1 billion Documents?

Thanks a lot!

------------------------------ Edit ------------------------------

I tried the insert with 10 million records on both MongoDB and MySQL, and MongoDB's behavior is about 20% faster than MySQL -- not really that much as I thought.

I am curious that, if I have the MongoDB Auto-sharding being setup, will the insert speed being promoted? If so, do I need to put the Shards on different physic machines, or I can put them on the same machine with multi- cores?

------------------------------ Update ------------------------------

First, I modified the MongoDB write concern from ACKNOWLEDGED into UNACKNOWLEDGED, then the MongoDB insert speed is 3X faster.

Later on, I made the insert program in parallel (8 threads with a 8-cores computer), For MongoDB ACKNOWLEDGED mode, the insert is also improved 3X, for its UNACKNOWLEDGED mode, the speed is actually 50% slower.

For MySQL, the parallel insert mode increases the speed 5X faster! Which is faster than the best insert case from MongoDB!

Answer 1

MongoDB queries without the index will be doing table scan and we should know that data size of mongodb as compared to mysql is much more. I am guessing this might be one of the issue for slowness when doing a full scan.

Regarding query with indexes, mongoDB may turn out faster because of caching, no complex query optimizer plan (like mysql) etc.

The size of the collection is not an issue. In fact 10 million can be easily be handled in one collection. If you are have the requirement of archiving data, then you can break into smaller collections which will make the process easy.