I am migrating a MySQL table which has 50 million more rows to a MongoDB database.
JSON Document example
Basically the problem I am facing is: This data will be shown on a HTML table and every column will be searchable and sortable (limiting 100 documents per pagination). I've created some testing databases on Mongo, which worked well when filtering and sorting only indexed fields. When there is filtering or sorting on non-indexed fields the querys runs too slow, because mongo need to scan the whole collection.
The solution I found to this problem on mysql was make a subselect limiting the filtered data, something like this:
select con.field1, con.field2
from (select * from myTable order by id desc limit 40000) as con
where con.field1 = '...' //another filters here
order by con.field2 desc
limit 100
This solution works very well on mysql, but I didn't found anything similar on Mongo. As I said, when I filter/sort on indexed fields, mongo querys runs very well, but I presume I can't create an index on every searchable/sortable field.
This is a query which runs fast on mongo, only filtering on indexed fields:
.find({
tipo_doc: 'nfe', //indexed field
id_empresa: 7 //indexed field
},
{
sort: {'data_requisicao': -1}, //indexed field
limit: 100
});
And this is a query which runs very slow:
.find({
tipo_doc: 'nfe', //indexed field
id_empresa: 7, //indexed field
'transportador.xNome': 'My name' //not indexed field
},
{
sort: {'emitente.enderEmit.UF': -1}, //not indexed field
limit: 100
});
What should I do to get a better performance in this case?