Indexes in Mongo

Question

Indexes in Mongo

Navigation

#1 by (0 votes)

0

I'm starting studies with 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 the fields, and I thought it would work ok, but I always get a "slow query" alert.

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

What is the best strategy for creating these indexes? {a:1, b:1}, {a:1, b:1, c:1}...

An example of a document in the database (only the 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
}

javascript node.js mongodb

asked by anonymous 15.12.2017 / 06:07

1 answer

Docker-compose to create lamp with phpunit Angular 4 - Input / Output Access parent event via Typescript

score 0 · Accepted Answer

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

Considerations:

Consider the order in which the fields are passed in the query. A% index of% is different from a% index of%.
To create index consider the following recommendations:
- First use the equality / difference fields (you use a direct value filter).
- Then consider the ordering you use in the query.
- And finally consider breaks.
- This way you make it easier for the optimizer to work, and if all the fields that you return are in the index the bank does not access the table only the index.

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

You can create some indexes with what you know / suppose to search. In the long run the best approach would be to monitor queries that are made more frequently, and for those to add specific indexes. So you do the bank work balancing to keep the indexes and the actual utilization of them.