MongoDB performance problem I have a MongoDB for storing result like this

{_id, type, imei, lat, lng, spd, dir, time}
{_id, type, imei, lac, cid, time}


If type is f first one is used and if it is l second one is used.

Here is the indexes:

"indexSizes" : {
    "_id_" : 340358704,
    "imei_1" : 614508160,
    "type_1" : 183616608,
    "type_1_imei_1" : 349793808
},


Collection has about 10 million documents. I have many queries on type and imei, and for now only types of f are used in applications.

This is a line from log file

Sun Aug 31 19:14:39.402 [conn782250] query xxyyxx.locs query: { $query: { $and: [ { imei: "359710042581612" }, { type: "f" } ] }, $orderby: { time: -1 } } ntoreturn:1 ntoskip:0 nscanned:140455 scanAndOrder:1 keyUpdates:0 numYields: 59 locks(micros) r:38456361 nreturned:1 reslen:159 19727ms


This took about 20 seconds, too much.

I need to optimize my collection more. This is used in a web application and sometimes queries like this are performed for 10 imeis, taking about 200 seconds, which may lead to a timeout. This is performed async (ajax) but still too much waiting time.

Any help from experts here is more than welcome.

Also, if you think I missed anything, please do request more information.

Thanks in advance

BTW, I'm using PHP for web part (Laravel framework and https://github.com/jenssegers/Laravel-MongoDB)

EDIT: Here is the explain (REMOVED)

EDIT2: After creating better indexes:

MongoDB shell version: 2.4.9
connecting to: test
&gt; use gpstracker
switched to db gpstracker
&gt; db.locs.stats()
{
    "ns" : "gpstracker.locs",
    "count" : 10137188,
    "size" : 1400944384,
    "avgObjSize" : 138.19852053646434,
    "storageSize" : 1580060672,
    "numExtents" : 19,
    "nindexes" : 2,
    "lastExtentSize" : 415145984,
    "paddingFactor" : 1,
    "systemFlags" : 0,
    "userFlags" : 0,
    "totalIndexSize" : 826806176,
    "indexSizes" : {
        "_id_" : 359997456,
        "type_1_imei_1_time_-1" : 466808720
    },
    "ok" : 1
}
&gt; db.locs.find({ $and: [ { imei: "359710042581612" }, { type: "f" } ] }).explain()
{
    "cursor" : "BtreeCursor type_1_imei_1_time_-1",
    "isMultiKey" : false,
    "n" : 150158,
    "nscannedObjects" : 150158,
    "nscanned" : 150158,
    "nscannedObjectsAllPlans" : 150158,
    "nscannedAllPlans" : 150158,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 329,
    "nChunkSkips" : 0,
    "millis" : 128173,
    "indexBounds" : {
        "type" : [
            [
                "f",
                "f"
            ]
        ],
        "imei" : [
            [
                "359710042581612",
                "359710042581612"
            ]
        ],
        "time" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "CDSX001:27017"
}
Your query is using the index to fetch the results but needs to sort them afterwards which is an expensive operation.

"scanAndOrder" : true, scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor.

The solution is to apply an index on {imei:1, type:1, time:-1} or {type:1, imei:1, time:-1} (the imei,type order on the index depends on imei and type selectivity)