Mongo using indexes with sort

Question

I'm trying to optimize a mongodb query. I have an index on from_account_id, to_account_id, and created_at. But the following query does a full collection scan.

{
    "ts": {
        "$date": "2012-03-18T20:29:27.038Z"
    },
    "op": "query",
    "ns": "heroku_app2281692.transactions",
    "query": {
        "$query": {
            "$or": [
                {
                    "from_account_id": {
                        "$oid": "4f55968921fcaf0001000005"
                    }
                },
                {
                    "to_account_id": {
                        "$oid": "4f55968921fcaf0001000005"
                    }
                }
            ]
        },
        "$orderby": {
            "created_at": -1
        }
    },
    "ntoreturn": 25,
    "nscanned": 2643718,
    "responseLength": 20,
    "millis": 10499,
    "client": "10.64.141.77",
    "user": "heroku_app2281692"
}

If I don't do the or, and only query from_account_id or to_account_id with an order on it, it's fast.

What's the best way to get the desired effect? Should I be keeping account_ids (both from and to) in one field like an array? Or perhaps there is a better way. Thanks!

jared · Accepted Answer · 2013-01-12 22:59:07Z

7

Unfortunately, as you have discovered, an $or clause can make life difficult for the optimizer.

So, to work around this you have a couple options. Among them:

Divide your query into two and manually merge the results.
Change your data model to allow efficient querying. For example, you might add a "referenced_accounts" field that is an array of all the accounts referenced in the transaction.

edited Jan 12, 2013 at 22:59

answered Mar 19, 2012 at 0:51

jared

5,5992 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spencer Over a year ago

$or in Mongo doesn't make optimizer skip indexes. It does skip indexes for nested $or statements: jira.mongodb.org/browse/…

Brian Armstrong Over a year ago

Btw, in case anyone was curious, I made a new Array field called referenced_accounts which auto updates using an after_update filter in rails. This single field can now be queried with an index. A little bit of duplication but it works well.

Collectives™ on Stack Overflow

Mongo using indexes with sort

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related