[SERVER-9077] How to optimize the query Created: 22/Mar/13  Updated: 10/Dec/14  Resolved: 25/Mar/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Arati Sethy Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Linux


Participants:

 Description   

I have a capped collection with 1.3 million documents indexed on id.
When i do a query with a filter it takes more than 1 minute to give back the result. The query includes find with sort on id and limit(20).
I need to get back the query result in max 30-45 seconds.
Let me know what enhancements can be done.
Below is explain output from shell. Same query i use from perl driver

{
	"cursor" : "BtreeCursor _id_ reverse",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 1308194,
	"nscanned" : 1308194,
	"nscannedObjectsAllPlans" : 2616388,
	"nscannedAllPlans" : 2616388,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 72,
	"nChunkSkips" : 0,
	"millis" : 109349,
	"indexBounds" : {
		"_id" : [
			[
				{
					"$maxElement" : 1
				},
				{
					"$minElement" : 1
				}
			]
		]
	},

Thanks



 Comments   
Comment by Andre de Frere [ 25/Mar/13 ]

Hi Arati,

Because you only have an index on _id the entire collection is being scanned when you are querying for

{ port : 2 }

- every document has to be checked for the key

{ port : 2 }

.

The explain output is indicating that the _id index is being used to sort the results, but the entire result set has to be brought back as there is no index on port.

If you ensure an index on port, your query will return a lot more quickly. You could do this with:

db.collectionname.ensureIndex( { port : 1 } )

A couple of other points.

  • There are no documents in your collection that have { port : 2 }

    (the n in your explain output is 0)

  • You do not need to skip(0), this would not actually do anything.
  • The indexing documentation has great detail on what indexing entails in MongoDB and is a good place to start research on indexes.
  • There are a number of presentations on the 10gen website on Indexing and Query Optimisation, which have good information on first steps on Indexing.
  • A good place to ask questions of this type is in the Google Group for MongoDB users
Comment by Arati Sethy [ 25/Mar/13 ]

Query is
db.collectionname.find(

{"port":2}

).skip(0).limit(20).sort(

{"_id": -1}

).explain().

Comment by Aaron Staple [ 22/Mar/13 ]

Hi Arati - Can you send the full query?

Generated at Thu Feb 08 03:19:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.