Speeding up MongoBD query { $ne: [] }

Question

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.

Answer 1

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).

Answer 2

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.