0

This may be a novice question but could it be possible query here.

If I have a compound Index as

{ a: 1, b: 1, c: 1, d: 1 }

Is this query possible?

db.data.find( { a: {$gte:1,$lt:2}, b: { $gt: 3} },c:"test",d:"yes" ).sort( { b: 1 } )

I mean can I use sorting on field b with this compound index and the specified query or do I need to create a separate index on b?

1

Compound indexes are great for static values

Flip the WHERE components so that static values are first

db.data.find( { c:"test", d:"yes", a: {$gte:1,$lt:2}, b: {$gt: 3} }).sort( { b: 1 } )

Create a different compound index

db.data.ensureIndex({c:1},{d:1},{b:1},{a:1})

How does it help ?

  • Static c value
  • Static d value
  • Sorted Range on b

One more thing

{$gte:1,$lt:2} is really {$eq:1}

I have discussed covering indexes and how the order of columns can changes things. In these posts, I talked about it from the MySQL perspective:

  • Thanks for input but will sorting work as b is neither first nor last index of teh compound index – viren Feb 24 '15 at 9:29
  • @viren The point here is that the "index selection" and the "order of terms in your query" are very important to MongoDB. The main question you need to ask yourself is "Which terms reduce my possible set of results the most?". That is what you want in your "query selection" as the primary concern. The MongoDB query optimiser "can" select an "alternate" index for "sorting purposes", but your prime consideration should always be on "what narrows down the result to the smallest set for the query", and as a first consideration. Sorting is an ancillary exercise. – Neil Lunn Feb 26 '15 at 10:06
  • @NeilLunn thanks for your input. Could you please add this as an answer i will update it then. – viren Feb 26 '15 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.