Can I do a MongoDB "starts with" query on an indexed subdocument field?

Tom Robinson picture Tom Robinson · Oct 29, 2014 · Viewed 85.2k times · Source

I'm trying to find documents where a field starts with a value.

Table scans are disabled using notablescan.

This works:

db.articles.find({"url" : { $regex : /^http/ }})

This doesn't:

db.articles.find({"source.homeUrl" : { $regex : /^http/ }})

I get the error:

error: { "$err" : "table scans not allowed:moreover.articles", "code" : 10111 }

There are indexes on both url and source.homeUrl:

{
    "v" : 1,
    "key" : {
        "url" : 1
    },
    "ns" : "mydb.articles",
    "name" : "url_1"
}

{
    "v" : 1,
    "key" : {
        "source.homeUrl" : 1
    },
    "ns" : "mydb.articles",
    "name" : "source.homeUrl_1",
    "background" : true
}

Are there any limitations with regex queries on subdocument indexes?

Answer

Adam Comerford picture Adam Comerford · Oct 29, 2014

When you disable table scans, it means that any query where a table scan "wins" in the query optimizer will fail to run. You haven't posted an explain but it's reasonable to assume that's what is happening here based on the error. Try hinting the index explicitly:

db.articles.find({"source.homeUrl" : { $regex : /^http/ }}).hint({"source.homeUrl" : 1})

That should eliminate the table scan as a possible choice and allow the query to return successfully.