Details
-
Question
-
Resolution: Done
-
Major - P3
-
None
-
2.0.2
-
MacBook Pro 2010 running Mac OS X Snow Leopard
Description
I am storing documents with 2 fields: "date" and "b". For "date" I want to check that it is greater than or less than a certain date. For "b" I want to know whether or not it exists.
I want to query on both fields simultaneously.
I created some test data as follows:
date = new ISODate();
for (var i=0; i < 5000000; i++) {
m = Math.floor(Math.random()*12);
d = Math.floor(1+Math.random()*28);
date.setMonth(m);
date.setDate(d);
db.bar.insert(
);
}
for (var i=0; i < 5000000; i++) {
m = Math.floor(Math.random()*12);
d = Math.floor(1+Math.random()*28);
date.setMonth(m);
date.setDate(d);
b = (Math.random() > 0.5);
db.bar.insert(
);
}
Then I run 2 tests.
Test 1) I put an index on date only, and do a query explain.
db.bar.ensureIndex(
{"date":1});
db.bar.find({"date":{$lte:date},"b":{$exists:false}}).explain();
Test 2) I put a compound index on date and b, and do a query explain.
db.bar.ensureIndex(
{"date":1,"b":1});
db.bar.find({"date":{$lte:date},"b":{$exists:false}}).explain();
Results of the tests: Having a compound index on "date" and "b" runs a bit slower, even though it scans less documents. Here is the output of the explain()s:
Test 1)
{
"cursor" : "BtreeCursor date_1",
"nscanned" : 6128089,
"nscannedObjects" : 6128089,
"n" : 3775151,
"millis" : 17420,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :
}
Test 2)
{
"cursor" : "BtreeCursor date_1_b_1",
"nscanned" : 3775309,
"nscannedObjects" : 3775151,
"n" : 3775151,
"millis" : 18100,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :
}