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