MongoDB slow find

[Pablo Yabo]

I got this collection created from mongoose:

var ethTransactionSchema = new mongoose.Schema({
    blockNumber: Number,
    blockHash: String,
    hash: String,
    transactionIndex: Number,
    from: String,
    to: String,
    value: String
});
ethTransactionSchema.index({ hash: 1 }, { unique: true });
ethTransactionSchema.index({ from: 1 });
ethTransactionSchema.index({ to: 1 });
ethTransactionSchema.index({ blockNumber: 1, transactionIndex: 1 });

ethTransactionSchema.index({ from: 1, to: 1, blockNumber: 1, transactionIndex: 1 });
ethTransactionSchema.index({ from: 1, blockNumber: 1, transactionIndex: 1});

ethTransactionSchema.index({ to: 1, blockNumber: 1, transactionIndex: 1 });
ethTransactionSchema.index({ to: 1, blockNumber: 1 });

ethTransactionSchema.index({ from: 1, blockNumber: 1 });
ethTransactionSchema.index({ from: 1, to: 1, blockNumber: 1 });
ethTransactionSchema.index({ blockNumber: 1 });
ethTransactionSchema.index({ transactionIndex: 1 });

ethTransactionSchema.index({ blockNumber: -1 });
ethTransactionSchema.index({ to: 1, blockNumber: -1 });
ethTransactionSchema.index({ from: 1, blockNumber: -1 });
ethTransactionSchema.index({ from: 1, to: 1, blockNumber: -1 });

ethTransactionSchema.index({ from: 1, to: 1, blockNumber: -1, transactionIndex: -1 });
ethTransactionSchema.index({ from: 1, blockNumber: -1, transactionIndex: -1 });
ethTransactionSchema.index({ to: 1, blockNumber: -1, transactionIndex: -1 });

If I execute this query:

find({$or: [from: '0x120a270bbc009644e35f0bb6ab13f95b8199c4ad', to: '0x120a270bbc009644e35f0bb6ab13f95b8199c4ad' ]}).sort({blockNumber: -1, transactionIndex: -1})




I get excellent performance:

It chooses indexes (from explain())

{

        "from" : 1,

        "blockNumber" : 1,

        "transactionIndex" : 1

}

and:

{

        "to" : 1,

        "blockNumber" : 1,

        "transactionIndex" : 1

}

But when executing this query:

find({$and: [{$or: [{from: '0x120a270bbc009644e35f0bb6ab13f95b8199c4ad'}, 

{to: '0x120a270bbc009644e35f0bb6ab13f95b8199c4ad'}]}, 

{blockNumber: {$lte: 1700000}}

]}).sort({blockNumber:-1, transactionIndex: -1}).limit(21)

I get poor performance and sometimes it takes more than 20 seconds to return results (most times takes less than a second). It chooses these indexes:

{

       "from": 1

{

and:

{

      "to": 1

}

If the time were predictable in less than a second it would be acceptable but the first query takes some times 20 or 30 seconds, which is completely unacceptable.

Can someone point me to a solution?

Thanks on advanced,

P/

[Pablo Yabo]

Thanks on avanced,

P/