how to make compound index for multiple column range queries

[thepeppe...@gmail.com]

I want to make a range query on multiple columns. for example:

db.dmnscore.find({price:{$lte:10}, "updated" : {"$gte" : new
Date(1304874799000)} ,"score" : {"$gte" : 0,"$lte" :
1000}).sort({score:-1})

Is it possible to make a proper compound index for this query?

Thanks

[Gaetan Voyer-Perrault]

MongoDB supports compound indexes:

http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeysIndexes

What exactly do you mean by "a proper compound index"?

- Gates

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[thepeppe...@gmail.com]

The problem is I found "Only use a range query or sort on one
column." : http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ#IndexingAdviceandFAQ-3.Onlyusearangequeryorsortononecolumn.

Is there any way to handle this useage?

Syd

On May 10, 2:15 am, Gaetan Voyer-Perrault <ga...@10gen.com> wrote:
> MongoDB supports compound indexes:http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeysIndexes
>
> What exactly do you mean by "a proper compound index"?
>
> - Gates
>

> On Mon, May 9, 2011 at 10:42 AM, ThePeppersStu...@gmail.com <

[thepeppe...@gmail.com]

I have to indexes:
1. db.dmnscore.ensureIndex({updated:-1})
2. db.dmnscore.ensureIndex({price:1, updated:1, score:-1})

The query is db.dmnscore.find({price:{$lte:10}, "updated" : {"$gte" :

new
Date(1304874799000)} ,"score" : {"$gte" : 0,"$lte" :
1000}).sort({score:-1})

This query will only use the first index. But with large amount of
data the first index is not fast enough. So that I need a good
compound index.

Syd

On May 10, 2:15 am, Gaetan Voyer-Perrault <ga...@10gen.com> wrote:

> MongoDB supports compound indexes:http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeysIndexes
>
> What exactly do you mean by "a proper compound index"?
>
> - Gates
>

> On Mon, May 9, 2011 at 10:42 AM, ThePeppersStu...@gmail.com <

[Gaetan Voyer-Perrault]

So first recognize that the following two indexes are *not* the same. The ordering of the fields is important.

2. db.dmnscore.ensureIndex({ price:1, updated:1, score:-1})

3. db.dmnscore.ensureIndex({ score:-1, updated:1, price:1})

Second, the indexes are not 100% deterministic. If a query could use multiple indexes the system has to make some guesses/tests on which indexes to use.

Take a look at Scott's answer here for more details:

http://groups.google.com/group/mongodb-user/browse_thread/thread/47abe92c328aa556

- Gates