72

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?

Tom Robinson
  • 8,348
  • 9
  • 58
  • 102
  • 1
    Should work, what happen when you do an explain? Have you tried to run it with a db allowing scan? (with an explain) – Tug Grall Oct 29 '14 at 15:09
  • I've tried running an explain but it was taking so long I had to kill it. The queries both return the expected results if i enable table scans. – Tom Robinson Oct 29 '14 at 16:34

1 Answers1

85

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.

Adam Comerford
  • 21,336
  • 4
  • 65
  • 85
  • This looks promising though I'm getting: error: { "$err" : "bad hint", "code" : 10113 } – Tom Robinson Oct 29 '14 at 16:35
  • 1
    That's the error you get when you specify an index that doesn't exist. Which suggests that either it is missing, not finished building yet (note background: true), or that you might have a typo – Adam Comerford Oct 29 '14 at 17:48
  • Good point, though I've confirmed that the index has finished building and still get the same error. Even more strangely, if I specify the name of a non-subdocument index, e.g. url, I get results back but they don't honour the regex query! For example: db.articles.find({"source.homeUrl" : { $regex : /^http/ }}).hint({"url" : 1}) – Tom Robinson Oct 30 '14 at 10:28
  • The results issue may be due to this bug: https://jira.mongodb.org/browse/SERVER-15235 which is currently not fixed, even in master as of writing this. The inability to hint is odd, and should be reproducible so I will see if I can make it happen - can you mention what version you are currently on? – Adam Comerford Oct 30 '14 at 10:49
  • We're using 2.4.9 currently. – Tom Robinson Oct 30 '14 at 13:19
  • Any luck reproducing? – Tom Robinson Nov 03 '14 at 17:10