Compound indexes, sorting, optimization

[Sergei Tulentsev]

Hello everyone,

So I have this collection, documents in which contain fields user, subtype and, of course, _id.

I am doing these types of queries:

• find({user: u}).sort({_id: -1}).skip(n).limit(m)
• find({user: u, subtype: st}).sort({_id: -1}).skip(n).limit(m)

So, I was wondering, what index could be best here?
I tried {user: 1, _id: -1} and it seems to work perfectly for case 1. At least, nscanned equals to n+m. For the other case, nscanned is roughly a half of all records for that user.

Then I thought that I can stick st in the middle of that index and get better performance for case 2 while maintaining good results in case 1. I was wrong.
{user: 1, subtype: 1, _id: -1} is good for case 2, but worse for the first query, it now scans all records for given user.

It's still much better than scanning the whole dataset, but not what I expected :-)

So, is there a single index that can help me? And also, why this second index behaves like that? I imagined it would still scan n+m records.


--
Best regards,
Sergei Tulentsev

[Kyle Banker]

Ideally you can have both indexes:

{user: 1, _id: -1}

And

{user: 1, subtype: 1, _id: -1}

The reason you have to scan all records with the second index on the
first collection is that you're sorting by _id, so you need to sort
all records for a given user manually. In the former case, the
optimizer was able to use the index to satisfy the sort.

On Dec 1, 9:48 am, Sergei Tulentsev <sergei.tulent...@gmail.com>
wrote:

> Hello everyone,
>
> So I have this collection, documents in which contain fields user, subtype
> and, of course, _id.
>
> I am doing these types of queries:
>

>    - find({user: u}).sort({_id: -1}).skip(n).limit(m)
>    - find({user: u, subtype: st}).sort({_id: -1}).skip(n).limit(m)