I have a query that’s performing poorly: { 'userId': 'xxxx', 'info.date': { $gte: [date], $lt: [date] } }
There is a compound index on the collection: { 'userId': 1, 'info.date': -1 }
I’m querying with an upper and lower bound on the date field, but when I look at the explain() results, the index scan is only using the upper bound, meaning it is fetching far more documents than it needs to. I understand that this is a limitation on multikey indexes - that is, indexes on array fields - but info.date is not an array field, just a field in an embedded document.
I tried querying on a date field in the document root and didn’t have the same problem. But it seems to me that info.date shouldn’t have this problem either, as it’s not in an array.
Is there any way to get around this, short of changing the whole document schema?