CODEWITHSUNDEEP
mongodb
abipc
abipc

Reputation: 1035

MongoDB - find query taking a lot of time

Here is what db.mycoll.find({MY_FIELD:"FALSE"}).explain() returns - 

"cursor" : "BtreeCursor MY_FIELD_1",
"isMultiKey" : false,
"n" : 843392,
"nscannedObjects" : 843392,
"nscanned" : 843392,
"nscannedObjectsAllPlans" : 843392,
"nscannedAllPlans" : 843392,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 17798,
"nChunkSkips" : 0,
"millis" : 636733,
"indexBounds" : {
    "MY_FIELD" : [
        [
            "FALSE",
            "FALSE"
        ]
    ]
},
"server" : "db-2:30000",
"filterSet" : false

And here is what db.mycoll.stats() returns - 

"ns" : "810.mycoll",
"count" : 1052404,
"size" : 4052704096,
"avgObjSize" : 3850,
"storageSize" : 4841168896,
"numExtents" : 24,
"nindexes" : 5,
"lastExtentSize" : 1257897984,
"paddingFactor" : 1,
"systemFlags" : 1,
"userFlags" : 1,
"totalIndexSize" : 215903632,
"indexSizes" : {
    "_id_" : 38484432,
    "fieldA_1" : 42155456,
    "fieldB_1_MY_FIELD_1" : 54255936,
    "fieldC_1" : 42343504,
    "MY_FIELD_1" : 38664304
},
"ok" : 1

My Quesns are -

  1. Why is it taking 10 mins (636 secs) even when I have an index on MY_FIELD?
  2. How do I fix this? Is there anyway find takes lesser time?

Stuff that I know -

  1. There is no contention (read/write) for this database. It has a total of around 1 million docs.
  2. MY_FIELD has only two possible values

Here is mongostat data

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time 
    *0      7    436     *0       0     7|0       0   304g   610g  2.12g     40   810:5.6%          0       0|0     3|1   257k   521k    21   20:39:35 
    *0      1    179     *0       0     3|0       0   304g   610g  2.12g     11   810:0.0%          0       0|0     1|1    99k   520k    21   20:39:36 
    *0     *0     53     *0       0     3|0       0   304g   610g  2.12g      3  1273:8.7%          0       1|0     1|1    28k     1m    22   20:39:37 
    *0     *0    805     *0       0     2|0       0   304g   610g  2.12g     44   810:0.0%          0       1|1     2|1   440k   519k    21   20:39:39 
    *0   2966    112     *0       0     1|0       0   304g   610g  2.12g     12   810:2.6%          0       0|0     1|1    63k   518k    21   20:39:40 
    *0      1    398     *0       0     1|0       0   304g   610g  2.12g      9   810:0.0%          0       1|0     1|1   238k   520k    21   20:39:41 
    *0     *0    987     *0       0     1|0       0   304g   610g  2.13g    101   810:0.0%          0       1|2     2|1   612k   518k    21   20:39:42 
    *0     11     86     *0       0    10|0       0   304g   610g  2.13g     17

Upvotes: 0

Views: 925

Answers (1)

arun
arun

Reputation: 11013

It is possible that though the query is using the index, it takes time to build the documents. explain is showing "indexOnly" : false which means the index is not a covering index (see http://docs.mongodb.org/manual/reference/method/cursor.explain).

You can try this. If you change your query to:

db.mycoll.find( {MY_FIELD: "FALSE"}, {_id: 0, MY_FIELD: 1} ).explain()

then the index on MY_FIELD itself can be used to construct your results. Check if this is running much faster. Obviously this query may not be of much use to you, since this does not return you the documents. This is just to figure out if the time is spent in constructing the documents and not for the search itself.

Also, if MY_FIELD is only going to have two values, you are better off using Boolean than string. The document size and index will be smaller.

Upvotes: 1

Related Questions