Performance problems modeling a MySQL to MongoDB Database with 50 million documents

Viewed 33

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?

0 Answers
Related