title: MongoDB query taking too much time to retrieve data
body: <p>I have one collection with 3 million documents and the following indexes:</p>
  
  <blockquote>
    <p>{ ts : 1 } , {u_id: 1}</p>
  </blockquote>
  
  <p>Note that these are two separate ascending indexes, not a compound index.</p>
  
  <p>When I run this query:</p>
  
  <blockquote>
    <p>db.collection.find({u_id: 'user'}).sort({ts : -1}).skip(0).limit(1)  </p>
  </blockquote>
  
  <p>it takes +100ms. I have the following logs:</p>
  
  <blockquote>
    <p>2017-04-15T06:42:01.147+0000 I COMMAND  [conn783] query<br>
    db.collection query: { orderby: { ts: -1 }, $query: {<br>
    u_id: "user-ki-id } } planSummary: IXSCAN { u_id:<br>
    1 }, IXSCAN { u_id: 1 } ntoreturn:1 ntoskip:0 keysExamined:10795<br>
    docsExamined:10795 hasSortStage:1 cursorExhausted:1 keyUpdates:0<br>
    writeConflicts:0 numYields:86 nreturned:1 reslen:771 locks:{ Global: {<br>
    acquireCount: { r: 174 } }, Database: { acquireCount: { r: 87 } },<br>
    Collection: { acquireCount: { r: 87 } } } <strong>246ms</strong>  </p>
  </blockquote>
  
  <p>A few notable points about the problem:</p>
  
  <ul>
  <li>There is no other load on MongoDB i.e. no other queries which take +100ms</li>
  <li>This is happening every minute; I think I am storing data every minute so this is happening</li>
  <li>The query flow is to first run the read query (as above), then the next query is a bulk insertion. This flow is repeated every one minute.</li>
  </ul>
  
  <p>So my questions are:</p>
  
  <ul>
  <li>Why is it happening? Are there any design flaws in my indexing?</li>
  <li>Might it be worthwhile to change indexing to be descending, like {ts: -1}? What is the actual difference between these indexes?</li>
  <li>According to MongoDB documentation, when you are doing sorting with order then result will pick from disk not "in-memory". Does this explain why it takes +100ms?</li>
  <li>Can anybody explain me profiling log in detail level?</li>
  <li>Is it desired behaviour of MongoDB?</li>
  </ul>
  
  <p>The same thing is also happening when I run a range search on this collection; this takes 3-5 seconds.</p>
  
  <p><strong>EDIT:</strong> 
  I have only add {u_id: 1, ts: -1} index. Remove all other index (except _id). Still in first time query execution taking +100ms. This should not happen.<br>
  Query:</p>
  
  <blockquote>
    <p>db.getCollection('locations') .find({u_id: "USR-WOWU"})
    .sort({ts: -1}) .explain(true)  </p>
  </blockquote>
  
  <p>OutPut:: </p>
  
  <blockquote>
    <p>/* 1 */ {
        "queryPlanner" : {
            "plannerVersion" : 1,
            "namespace" : "db_name.collection_name",
            "indexFilterSet" : false,
            "parsedQuery" : {
                "user_id" : {
                    "$eq" : "USR-WOWU"
                }
            },
            "winningPlan" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "u_id" : 1.0,
                        "ts" : -1.0
                    },
                    "indexName" : "u_id_1_ts_-1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "u_id" : [ 
                            "[\"USR-WOWU\", \"USR-WOWU\"]"
                        ],
                        "ts" : [ 
                            "[MaxKey, MinKey]"
                        ]
                    }
                }
            },
            "rejectedPlans" : []
        },
        "executionStats" : {
            "executionSuccess" : true,
            "nReturned" : 164,
            "executionTimeMillis" : 119,
            "totalKeysExamined" : 164,
            "totalDocsExamined" : 164,
            "executionStages" : {
                "stage" : "FETCH",
                <strong>"nReturned" : 164,</strong>
                <strong>"executionTimeMillisEstimate" : 120,</strong>
                "works" : 165,
                "advanced" : 164,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 3,
                "restoreState" : 3,
                "isEOF" : 1,
                "invalidates" : 0,
                "docsExamined" : 164,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 164,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 165,
                    "advanced" : 164,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 3,
                    "restoreState" : 3,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "u_id" : 1.0,
                        "ts" : -1.0
                    },
                    "indexName" : "u_id_1_ts_-1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "u_id" : [ 
                            "[\"USR-WOWU\", \"USR-WOWU\"]"
                        ],
                        "ts" : [ 
                            "[MaxKey, MinKey]"
                        ]
                    },
                    "keysExamined" : 164,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0
                }
            },
            "allPlansExecution" : []
        },
        "serverInfo" : {
            "host" : "manish",
            "port" : 22022,
            "version" : "3.2.13",
            "gitVersion" : "23899209cad60aaafe114f6aea6cb83025ff51bc"
        },
        "ok" : 1.0 }</p>
  </blockquote>
  
  <p>Please copy above jSON and format into any editor. </p>
  
  <p>After above query, the next same query will response with in ~2 ms. But When I do  few insertion then after one min same thing will be repeated. (1st time query will take time +100ms and then it will take ~2ms.)</p>
  
  <p>Is something missing or anything is required to configuration in my mongoDB ?? </p>