5

I'm trying to store the following link:

URL = {
  hostname: 'i.imgur.com',
  webid: 'qkELz.jpg'
}

I want a unique and sparse compound index on these two fields because:

  1. A combination of hostname and webid should be unique.
  2. webid will always be queried with hostname.
  3. webid need not be globally unique.
  4. A URL need not have a webid.

However, when I do this, I get the following error:

MongoError: E11000 duplicate key error index: db.urls.$hostname_1_webid_1  dup key: { : "imgur.com", : null }

I guess in the case of compound indexes, nulls are counted, whereas in regular indexes, they are not.

Any way out of this problem? For now I'm just going to index hostname and webid separately.

Jonathan Ong
  • 19,927
  • 17
  • 79
  • 118
  • 1
    For [unique indexes](http://www.mongodb.org/display/DOCS/Indexes#Indexes-unique%3Atrue) (compound or otherwise) you cannot have identical keys. You should be able to insert a single record with (hostname, null) but the second with identical hostname will be a duplicate. – Stennie Jul 09 '12 at 04:23

1 Answers1

5

Keep in mind that mongodb can only use one index per query (it won't join indexes together to make a query on two fields that have separate indexes faster).

That said, if you want to try to check for uniqueness, you could do a query from the app before inserting (which only partially solves the problem, because there's a gap between when you query and when you insert).

You might want to vote on this JIRA issue for filtered indexes, which will probably help your use case: https://jira.mongodb.org/browse/SERVER-785

Eve Freeman
  • 32,467
  • 4
  • 86
  • 101
  • oh. so how does it decide which index to work on? i already check for uniqueness before inserting. – Jonathan Ong Jul 08 '12 at 22:41
  • It has a couple different query plans that it choses from based on the collection and the query: http://www.mongodb.org/display/DOCS/Optimization – Randall Hunt Jul 08 '12 at 23:20