MongoDB query faster lte than gte

There are some issues with what you are trying to do:

1. $or queries use indexes differently

   For $or queries to be able to use an index, all terms of the $or query must have an index. Otherwise, the query will be a collection scan. This is described in https://docs.mongodb.com/manual/reference/operator/query/or/#or-clauses-and-indexes

2. Too many indexes in the collection

   Having too many indexes in a collection affects performance in more than one way, e.g., insert performance will suffer since you are turning one insert operation into many (i.e. one insert for the collection, and one additional insert for each indexes in your collection). Too many similar-looking indexes is also detrimental to the query planner, since it needs to choose one index out of many similar ones with minimal information on which index will be more performant.

3. Check the explain() output in mongo shell

   The explain() output in the mongo shell is the best tool to discover which index will be used by a query. Generally, you want to avoid any COLLSCAN stage (which means a collection scan) and SORT_KEY_GENERATOR stage (which means that MongoDB is using an in-memory sort that is limited to 32MB, see https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/). Please see Explain Results for more details.

You may want to check out these relevant pages regarding indexing and query performance:

• Analyze Query Performance
• Use Indexes to Sort Query Results
• Indexing Strategies

Having said that, I did a quick check in mongo shell (MongoDB 3.2.8) using your examples. You could try adding these two indexes in your collection:

{blockNumber:1,transactionIndex:1,from:1}{blockNumber:1,transactionIndex:1,to:1}

And drop all the other indexes in the collection. Those two indexes should be able to be used in your two examples queries.