indexing question

[Lincoln]

Hi, I'm using Mongo 1.3.3 and I'm having a problem getting a query to use an index.

The index:

{

"name" : "state_1__id_1_editorial_1",

"ns" : "hotpotato_integration.events",

"key" : {

"state" : 1,

"_id" : 1,

"editorial" : 1

}

}

The find query:

------------

{

  "state":{

    "$ne":"offline"

  },

  "_id":{

"$nin":["4b8e0970b82404d92ae40db2","4b8e09a1b82404d93be40db2","4b8e046cd9a604d9ecb18020","4b8e0402d9a604d9dbb18020","4b8e03d2d9a604d9ceb18020","4b8acc6e35b304d9dbdf6d90","4b8c244d468e04d925c6b191","524ce9648181854b12a6d300","4b8c1c9a295604d909d49eda","4b8b05cf7f1104d9ffc2fea4","4b8b317f7f1104d9f3c7fea4","7c4ce9645f36874b25631900","4b8ab26f9bf204d9cc154b17","4b880d8e3b3004d9cba7c284","4b88a38ead2504d92d82001c","4b884da8c5b904d9d7892ecc","4b87d9c4f92904d94a148603"]

  },

  "editorial":{

    "$gt":0

  }

}

The fields:

------------

{"name":1,"description":1,"photo_url":1,"permalink":1,"visibility":1,"state":1,"created_by":1,"location":1,"starts_at":1,"checkin_available":1,"activity":1,"iphone_ctx":1}

And the Explain output:

------------

{

  "cursor":"BasicCursor",

  "startKey":{

    

  },

  "endKey":{

    

  },

  "nscanned":135.0,

  "n":0,

  "millis":0,

  "allPlans":[{

    "cursor":"BasicCursor",

    "startKey":{

      

    },

    "endKey":{

      

    }

  }]

}

Am I missing something basic?

Thanks,

Lincoln

[Eliot Horowitz]

indexes generally aren't very helpful with a $ne since normally that
means most of the index matches.
Makes it hard to use in an intelligent way.

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/mongodb-user?hl=en.
>

[Lincoln]

Alright thanks for the clarification.  I'll handle that part in memory.

Thanks, 

Lincoln.

[Lincoln]

Actually, am I going to have the same problem with $nin?  Should I try simply indexing on 'editorial' and do that part of the query first so that it can use the index at least partially?

[Eliot Horowitz]

Yeah - I would put editorial first in the index.
If you put all 3 fields in the index, even if it doesn't reduce index
scan size, it'll prevent having to load the full object off disk.
which reminds i was going to add a way to see that in explain
added a case: http://jira.mongodb.org/browse/SERVER-702

[Lincoln]

Thanks Eliot.  Does the fact that I'm doing a range query on editorial impact your recommendation?  I thought I remember from a previous conversation that range queries should go last.  

Also could you explain a bit more how having all three fields in the index prevents loading the full objects off disk?  Is it just that they don't need to load the fields in the index?  Confused...

Thanks,

Lincoln

[Eliot Horowitz]

If you have 3 fields, and 2 are simple equalities, and 1 is range,
then the range should go last.
In this case, the range is really the only one that can be used, so it
should go first.

It doesn't have to load the object off disk because the values it
needs to check are in the index. So even though it has to look at a
lot of keys in the index, can make the decision about whether to
return the object or not solely from the key, and therefore will be a
lot faster.

[Lincoln]

OK now I understand.  Thanks Eliot.

[Lincoln]

Hi sorry, one more question on this: How does index ordering impact sorting?  If I have 2 fields I'm querying on and a third I'm sorting on, but the 2nd query field is a $ne or $nin, should I still preserve the order as first query field, second query field, sort field?

Thanks,

Lincoln

[Eliot Horowitz]

Any field that will only be used for ne or non should be last

So in that case you want

(constant,sort,nin)

And that should work decently with the query optimzer)

[Lincoln]

OK that wasn't what I was doing so I'm glad I asked. Thanks again.

Lincoln

[Nicolas Clairon]

Very interesting topic. Having it in the documentation should help a
lot of people.

[Lincoln]

So Eliot, does the order of fields in a query matter with respect to how the index is structured?  Or is it simply that it's important to structure the index with the most significant fields first and after that the order of fields in a query don't matter?

Thanks,

Lincoln

[Michael Dirolf]

Correct, the order of fields in a query doesn't matter - just
important to get it right in the index itself.

[Eliot Horowitz]

The order in the query doesn't matter, only the index.

On Fri, Mar 5, 2010 at 11:22 AM, Lincoln <linxb...@gmail.com> wrote:

[Lincoln]

OK and for updates, is it appropriate to generally assume that the benefit of indexing the query is not worth the increased time to change objects, particularly for updateMulti?  Or is it more case-specific i.e. only if you're changing fields that are in an index?

Thanks,

Lincoln

[Eliot Horowitz]

Depends on the update.
For updates that don't increase the size of an object, only an index
delta is created so its very efficient.
Some updates ($inc, some $set) on non-indexes fields just are done in
place, and never touch indexes.
So most of the time, having the index is better.

[Lincoln]

Alright thanks.  Sorry for the battery of questions but you really helped clear things up.

Thanks,

Lincoln

[Eliot Horowitz]

No problem - we should have better docs on this.
One thing to remember is that indexing in mongo is incredibly similar
to mysql or any other relational db. SO when in doubt, those docs are
probably correct.