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?

ChrisF
  • 302
  • 1
  • 5
  • 17
viren
  • 511
  • 2
  • 9
  • 29

1 Answers1

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:

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536