Mongodb – compound array index mongodb

mongodbnosql

I just realised you can't have a compound index with more than one array with mongodb.
I'm doing a tags,categories and actors search and I have simply stored them as arrays in the document example :

{
url = whatever.com
tags = [tag1,tag2]
actors = [actor1]
categories = [categorie1,categorie2]
}

How should I store them to make the search as fast as possible ? A search of this form:

tag1 AND tag 2 … AND tagN AND categorie1 AND categorie2 … AND categorieN AND actor1 AND actor2 … AND actorn

Where the documents returned would be the videos that all have AT LEAST(so all) those criterias.

Best Answer

You're correct that MongoDB can index only one array per document. This is called Multikey index. The limitation is due to the fact that MongoDB will create one index entry per array element.

So for example if you have an array with 10 elements, MongoDB will create 10 index entries for that single document. If you index more than one array, you can imagine that the number of index entries will go up exponentially, making the overall index impractically large.

One solution to your question is to refactor your document to use only one array, with the key tag, actors, and categories acting as a sub-document key. For example:

> db.test.find()
{
  "_id": ObjectId("597820ef2dbe1d773beb11d4"),
  "url": "whatever.com",
  "values": [
    {"tag": "tag1"},
    {"tag": "tag2"},
    {"actor": "actor1"},
    {"actor": "actor2"},
    {"category": "category1"},
    {"category": "category2"}
  ]
}

You can index this document easily since it only contains one array.

To search for the condition you proposed: tag1 AND tag 2 ... AND tagN AND categorie1 AND categorie2 ... AND categorieN AND actor1 AND actor2 ... AND actorn, you can use the $all operator. For example:

> db.test.find({
    values: {$all: [
        {tag: 'tag1'},
        {tag: 'tag2'},
        {actor: 'actor1'},
        {actor: 'actor2'},
        {category: 'category1'},
        {category: 'category2'}
    ]}
})

Which will return the document shown above.

Please see Query an Array for more methods in querying an array in MongoDB.

Also please see Explain results for methods to observe and optimize your query performance.

Related Solutions

Mongodb – Schema design for privacy settings in MongoDB

It's important to remember that since there are multiple ways to design schema in MongoDB for a given set of data, it's critical to consider the types of reads and writes you will be making from your application.

Assuming that you will be querying for a subset of documents that a given user can see, it's likely that you will want to structure the document collection in a way that will give you the ability to query for all "visible" documents in a single query.

Document structure such as:

{ _id: ...,
  public: true,
  groups: ['group1', 'group2',...],
  users: ['user1','user2',...],
}

You could now query for all documents visible to user X if you have a list of groups user X belongs to by querying:

db.documents.find( { $or : [
     { public : true },
     { users  : X },
     { groups : { $in : [list-of-X's-groups] } }
] } )

MongoDB text index and text search

So, no need to test as it turns out, I just remembered the key deciding limitations. You can only create one text index on a collection (reference here), so you have no real choice. Additionally, MongoDB can only use one index at a time to satisfy a query (until index intersection is introduced in 2.6).

Hence, the only workable option is to create the compound index as you outlined, on both fields as a single index:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

The same remains true for 3 or 4 fields, you would first have to drop the existing index, then create the new index and include 3/4/other.

Best Answer

Related Solutions

Mongodb – Schema design for privacy settings in MongoDB

MongoDB text index and text search

Related Question