Mongo indexes

Question

I am starting the studies with the indexes. I have a base in Mongodb where the documents have about 10 fields. Searches can be done using all these variables.

I created an index with all fields, and I thought it would work ok, but I always get a "slow query alert".

Do I need to create Dexes for all field combinations? Some of these will be in all queries.

What is the best strategy to create these Dexes? {a:1, b:1}, {a:1, b:1, c:1}...

An example document in the database (only fields that can be filtered):

{
    "id": "12345",
    "objectivo": 1,
    "tipo": 1,
    "preco": 50000,
    "num_quartos": 2,

    "estado": "São Paulo",
    "cidade": "São Paulo",
    "endereco": "Rua...",
    "referencia": "12345",
    "updated": 1
}

Answer 1

Short answer to your question: yes, each combination needs an index.

Considerations:

• Consider the order in which fields are passed in the query. An index {a:1,b:1} is different from an index {b:1,a:1}.
• To create the index considers the following recommendations:
  • First use the equality/difference fields (you use a direct filter of a value).
  • Then consider the ordering you use in the query.
  • And finally consider intervals.
  • This way you facilitate the work of the optimizer, and if all the fields you return are in the index the database nor access the table only the index.

If you have a query with the following filter {preco: { $gt: 3250}, cidade:"São Paulo"} ordering by {num_quartos: -1} the creation of the index would be {cidade: 1, num_quartos: -1, preco: 1}. Note that in the query the order does not matter, but in the index makes a lot of difference.

You can create some indexes with what you know/assume will be searched. In the long run the best approach would be to monitor the queries that are made more frequently, and for these to add specific indexes. So you do bank work balancing to keep indexes and their actual use.