MongoDB performance problem

Question

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
> use gpstracker
switched to db gpstracker
> 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
}
> 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"
}

• Stennie: If all your documents have a type value of f, it isn't useful to include in the index. An index only helps with criteria that is selective and helps limit results. If your typical query is on imei ordered by time (descending), I would add an index on { imei: 1, time: -1}. Also, you do not need $and in your example query; this is implicit in a default query. What version of MongoDB are you using? Have you tried using explain() to look at the query plan?
• Stennie: Also, you don't need to have an index on both type:1 and { type: 1, imei:1 } as per your index list. A compound index on {type:1, imei:1} can be also be used to satisfy queries on type:1 (or more generically, any proper subset of keys that match the order of a compound index from left to right).
• vfsoraki: Good points on indexes, thanks. So it is safe to removed indexes om imei and type (but not the combined one). I only use types of f, but I store types of l too. They will be used later. I will try adding index on time too, and see the results.
• vfsoraki: And about $and, this is not a query by me. It is generated by framework. May this have any performance impacts? If it has, I will look into using my own queries.
• Antonios: Can you execute an explain on your query and post the results? Regarding indexes This index"type_1" is covered by "type_1_imei_1" and can be safely removed.

Answer 1

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)

• Antonios: After the proposed index creation remove type_1_imei_1. Plus remove if you choose {imei:1, type:1, time:-1} index imei_1 or if you choose {imei:1, type:1, time:-1} index type_1 since they are covered from the new index.
• vfsoraki: Thanks, it worked! And I learned more on indexes. Now my AJAX request to server which executes about 20 queries like mentioned only takes 500 ms, very good. BTW, I think I will move documents of type l to a new collection. They have no use for now and are only decreasing performance. Thanks again! I also added new explain and index to question.