CODEWITHSUNDEEP
regexmongodbsearchindexingfull-table-scan
Tom Robinson
Tom Robinson

Reputation: 8528

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

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?

Upvotes: 76

Views: 139337

Answers (1)

Adam Comerford
Adam Comerford

Reputation: 21692

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.

Upvotes: 93

Related Questions