Using 2 indexes

[Philippe DAVID]

Hi,
I guess this question has been asked already but I did not find the answer.
I have a collection with an index on field1 and another index on field2.

I ran a find with:
find({ field1: { $gte: xxx, $lte: yyy }, field3: { $ne: null },
field2: "xxx"}, field4: /regex/).explain()

and I got:
{        "cursor" : "BtreeCursor field1_1",        "nscanned" :
3701529,        "nscannedObjects" : 3701529,        "n" : 10043,
"millis" : 2563332,        "nYields" : 48555,        "nChunkSkips" :
0,        "isMultiKey" : false,        "indexOnly" : false,
"indexBounds" : {                "field1" : [                        [
                              xxx,                                yyy
                      ]                ]        }}
Looks like it only used the index on field1, not the one on field2.

My question is: can Mongo merge the inverted lists of 2 indexes to
make the query faster or do I need to create compound index ?
If I create a compound index on field1 + field2, would it work in that case ?

Thanks,

--
Philippe
Allgoob SA

[Andreas Jung]

MongoDB can only use *one* index at a time.
Look into compound indexes

-aj

[Max Schireson]

it does not directly intersect the indexes but should try iterating both and use the one which is more selective.

a compound index should be faster. if field 1 and field 2 provide most of the selectivity that should make your query fast.

over time we will make our query optimizer smarter.

-- Max

--
Philippe
Allgoob SA

--
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.

[Philippe DAVID]

Ok, thank you for the answer.

Another question, does $ne make use of an index.
For instance I have field3: { $ne: null }, in my query.

--
Philippe
Allgoob SA

[Andreas Jung]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Philippe DAVID wrote:
> Ok, thank you for the answer.
>
> Another question, does $ne make use of an index. For instance I have
> field3: { $ne: null }, in my query.

a NOT operation usually does not work with indexes for most
databases.

- -aj

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJPITYxAAoJEADcfz7u4AZjrDYLv2/UnKLIcIcZvr5kKVXHWqSw
VrkGVW7Jpue7G6lRhQgoDCEoSR1MQwojJMYRjjmx1nt406/Z56O+PGgJlTIBjPS4
hvusJ1eYgvvLxllSM84GSWjbPRUVc+pvi0LUxSXujDAyt8y33icN+Vn+1tRYom6O
fy4s+x7jebdf+dRojlYRO1ClOQFw07660bdkADY+0TNs9cQ+WjGCexWjJ67h4qKG
v3z6XHcCf972QtyYOoyjDOKyOA6R1FsR0GAjEwrfILtWEayOwRwqnigNQeI8UhTB
kc5/bm4ZUuD8HWlQIcGz3/N66c7coC2X+Il4NvSs2F5PN9RI8yVApxKv1RAbYQ8/
jyEacosncH0bxA9M4Z5GFwTI9kOXdunQbufIMsB0MCzy4F9hR+QS8TZfOFxqMKET
8qPCYKjZeohFz7f8u7pwOyJsw9hJ0yQkbYM+yuFEaIPcfEIsjUNbYONiUDkefOSS
fNtkKxhK60JDHrvjvOVzn5+XuDqKQNk=
=z7yD
-----END PGP SIGNATURE-----

[Max Schireson]

See http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ#IndexingAdviceandFAQ-I%27musing%24neor%24nininaquery%2Candwhileitusestheindex%2Cit%27sstillslow.What%27shappening%3F

MongoDB can use indexes for $ne and $nin queries. But if most of the values are not null it won't be selective and thus won't be very helpful unless its part of a compound index.

Also note that index behavior for missing/null values is different with sparse indexes.

-- Max

[Max Schireson]

Not equal queries are hard to evaluate off of search engine style inverted indexes but not particularly hard off btree type indexes. Without knowing the mind of other database developers, it may have been left out because it was deemed not useful. Personally I have found application developers to make great creative use of database features :)

-- Max

[Chris Winslett]

In your query, you've got a lot of scenarios that are tough (but not
impossible) to optimize with Mongo:

> { field1: { $gte: xxx, $lte: yyy }

Because it is a range, this should be the last field in your indexes.
Even if it is the most used.  Because of this, you will typically have
multiple compound indexes.

>  field3: { $ne: null }

Will always be slow because $ne is inefficient.

>  field2: "xxx"

Fine :)

>  field4: /regex/

Unanchored pattern matching typically turns into table scans.

Recommendation:
Instead of trying to find a perfect indexing pattern for the query
above, I would modify my schema to make it easier to index. Without
"easier to index", you could potentially spend more money on RAM each
month.

Chris