3

I'm currently doing a MongoDB aggregation but then have a query looking for all documents where a certain field, whose value is an array, is empty.

obs = db.collection.count({'things.titles': {'$ne': [] }})

To ensure this query uses an index, do I just need to do something like this?:

db.collection.ensureIndex({'things.titles': 1})

I understand this creates a multikey index, but it still takes a very long time (more than an hour) on a collection sized at 4739208 documents.

3
  • How long is it taking for db.collection.count({'things.titles': [] }). Also read docs.mongodb.org/manual/core/query-optimization/…
    – titogeo
    Apr 20, 2016 at 12:04
  • Oddly, a brief few seconds @titogeo. Apr 20, 2016 at 14:51
  • In my tests using size is faster by 3 times than using ne - db.collection.count({'things.titles': { $size : 0}}); Is (db.collection.count() (minus) db.collection.count({'things.titles': { $size : 0}})) faster than using ne. My data size is small.
    – titogeo
    Apr 21, 2016 at 5:17

2 Answers 2

0

Your question specifies:

... a query looking for all documents where a certain field, whose value is an array, is empty.

Your query is count all documents where the array is not empty, is this intended?

Depending upon the cardinality of the values of this field, this could be causing these values to be expensive.

To query for empty arrays, the operation would be:

 db.collection.explain().count( 'things.titles' : [ ] } )

Note that there are edge cases to this query. For example, the following would be returned:

  { "_id" : ..., "things.titles" : [ [ ] ] }

The index usage of the count operation can be determined using the collection.explain() function.

For example:

 db.collection.explain().count({'things.titles': {'$ne': [] }})

The output will provide information showing the index bounds being used (if they exist).

0

Consider an example document field array_field: [ 32, 1, 999 ]. Create an index on the array field (index on an array field is called as Multikey Index) named as "array_field_ix_1".

The following query does not use the index:

db.collection.count( { array_field: { $ne: [] } } )

You can verify the index usage by generating a query plan using explain():

db.collection.explain().count( { array_field: { $ne: [ ] } } )

You will note the 'COLLSCAN' (Collection scan) in the plan output:

... winningPlan: {
      stage: 'COUNT',
      inputStage: {
        stage: 'COLLSCAN',
        filter: { array_field: { '$not': { '$eq': [] } } }, ...

By modifying the query predicate you can have the query use the index; note the 'IXSCAN' (scanning index keys) in the explain plan output:

db.collection.explain().count( { array_field: { $gt: [ ] } } )

... winningPlan: {
      stage: 'COUNT',
      inputStage: {
        stage: 'FETCH',
        filter: { array_field: { '$gt': [] } },
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: { array_field: 1 },
          indexName: 'array_field_ix_1', ...

You are likely to notice the improvement in query performance when the query is using an index.


Here is another option to try. This also may produce better performance.

There is an index type called as Partial Index:

Partial indexes only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection, partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance.

The index creation syntax is db.collection.createIndex(keys, options), where the option is partialFilterExpression. So, this can be created on a compound index, including one more field other than the field used in the filter expression.

For example, suppose your collection has documents with another field called age and its values are always greater than zero. We can create a partial index as:

db.collection.createIndex(
   { age: 1 },
   { name: "my_partial_ix", partialFilterExpression: { array_field: { $gt: [] } } }
)

And the following query and its explain plan output:

db.collection.explain().count({ age: { $gt: 0 }, array_field: { $gt: [] } })

... winningPlan: {
      stage: 'COUNT',
      inputStage: {
        stage: 'COUNT_SCAN',
        keyPattern: { age: 1 },
        indexName: 'my_partial_ix', ...

The 'COUNT_SCAN' stage indicates the index usage for the count operation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.