MongoDB extremely slow performance when filtering or sorting on a non indexed field

Viewed 537

MongoDB is running find() operations very slow when I sort or search on non-indexed fields. When there is not sorting or searching on these fields or this operations are made on indexed fields the query runs very fast. My test database has only 650k documents (which is not very much), but the final database will have 50 million documents growing 30k/day.

This is a query which runs fast (less than 1s), only filtering on indexed fields:

.find({
    tipo_doc: 'nfe', //indexed field
    id_empresa: 7 //indexed field
},
{
    sort: {'data_requisicao': -1}, //indexed field
    limit: 3000
});

And this is a query which runs very slow (30s or more):

.find({
        tipo_doc: 'nfe', //indexed field
        id_empresa: 7, //indexed field
        'transportador.xNome': 'A commom name' //not indexed field
    },
    {
        sort: {'emitente.enderEmit.UF': -1}, //not indexed field
        limit: 3000
    });

Here's a JSON document example: https://pastebin.com/1EeaL9VL

I've made a lot of researchs but nothing seems to work here.. I am doing something wrong to get this poor performance?

EDIT:

As I said in my comment, this data will be shown on a HTML table, and every column will be searchable and sortable, so I assume I can't create an index on every column..

0 Answers
Related