$OR Queries not hitting a composite index

[jdmerth]

I have a query that seems to be ignoring a composite index. The 

I have the following index:

db.mytable.ensureIndex({

"odadd.n": 1,

"odadd.cmp": 1,

"adadd.Address.n": 1,

"adadd.Address.cmp": 1

})

and when running the query:

db.mytable.find({

"$or": [{

"odadd.n": "AMY"

},

{

"odadd.cmp": "AMY"

},

{

"adadd.Address.n": "AMY"

},

{

"adadd.Address.cmp": "AMY"

}]

mongo does a table scan.  I can get it to work if I add separate indexes on each field, but that seems like a poor solution. Can anyone provide some insight into what I'm doing wrong here?

Thank you!

[jdmerth]

Thanks for the clarification.

I created individual indexes to cover the fields, which works...but when I try to query against composite indexes I'm getting a table scan.

for example, I have the following indexes:

db.packages.ensureIndex({

"odadd.l1": 1,

"odadd.c": 1,

"odadd.s": 1,

"odadd.z": 1

})

db.packages.ensureIndex({

"adadd.l1": 1,

"adadd.c": 1,

"adadd.s": 1,

"adadd.z": 1

});

then for the query:

 db.packages.find({

"$or" : [

{

"odadd.z" : "76087"

},

{

"adadd.z" : "76087"

}

]

}).explain();

I'm still getting a table scan.  I can get it to work if I create individual indexes on the 'z' field, but it seems like it should use the composite index.

Thanks in advance!

On Thursday, August 30, 2012 12:21:07 PM UTC-6, thomasr wrote:

Queries with $or use multiple index plans in parallel, so you actually want a separate index on each of the fields of the $or query. This is the exception to normal queries, that need one compound index per query.

http://www.mongodb.org/display/DOCS/Indexing+Advice+and+FAQ#IndexingAdviceandFAQ-Oneindexperquery.

[Thomas Rueckstiess]

You need a separate index for each of the fields in the or clause. If your query reads  { $or: [{odadd.z: ...}, {adadd.z: ...}] }, then you need two indexes, one on odadd.z and one on adadd.z.

$or works different from other query operators, it runs the separate indexes in parallel.

[Max Schireson]

Yes.

To explain a little further how the indexes work why that's the case, an index on a, b is generally not useful for a query on b, since it is ordered by a first then secondarily b so it can't find things just on b. So when one of the clauses of the or is on b, it can't use that index.

I hope that helps,

-- Max

--
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
See also the IRC channel -- freenode.net#mongodb