my test collection has these fields

{
    "block_number": 123123,
    "value": 555.55555
    "transfers": [
        {
            "from": "foo1", 
            "to": "bar1", 
            "amount": 111.111
        },
        {
            "from": "foo2", 
            "to": "bar2", 
            "amount": 222.222
        },
        ...
        {
            "from": "foo3", 
            "to": "bar3", 
            "amount": 100.100
        },
    ]
},
...
{
    ...
}

and I made indexes like this (at python code)

tx_collection.create_index("block_number")
tx_collection.create_index("transfers.amount")
tx_collection.create_index("value")

Why two similar queries show different speed?

two queries are

1. tx_collection.find({'block_number': {'$gte': 3243145}}, {'_id': 0}).sort('transfers.amount', -1).limit(100)
2. tx_collection.find({'block_number': {'$gte': 3243145}}, {'_id': 0}).sort('value', -1).limit(100)

And execution time test python code is like this

    start = time.time()

    txs = tx_collection.find({'block_number': {'$gte': 3243145}}, {'_id': 0}).sort('transfers.amount', -1).limit(100)
    # txs = tx_collection.find({'block_number': {'$gte': 3243145}}, {'_id': 0}).sort('value', -1).limit(100)

    for tx in txs:
        print(tx)

    done = time.time()
    print(done - start)

execution time

• 1st query : about 0.1 sec
• 2nd query : about 10 sec

Why are they too different?

JFYI. the mongdb has 5 sharded and I run this query onto mongos using python code.

Additional test.

When I test below query on mongodb client,

db.getCollection('transaction').find({}).sort({"transfers.amount":1})

it throws error message like

"Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.",

It seems like index on transfers.amount seems like not working

When I checked with db.getCollection('transaction').getIndexes(), I confirmed that index on transfers.amount itself has been set well, like

    {
        "v" : 2,
        "key" : {
            "transfers.amount" : 1.0
        },
        "name" : "transfers.amount_1",
        "ns" : "crossangle.transaction"
    }