Question 118543 in Mongodb Question posted in
The official documentation can be found here.

How does mongodb decide which index to use for a query?

When a certain query is done on a mongodb collection, if there are multiple indexes that can be used to perform the query, how does mongodb choose the index for the query?

for an example, in a ‘order’ collection, if there are two indexes for columns ‘customer’ and ‘vendor’, and a query is issued with both customer and vendor specified, how does mongodb decide whether to use the customer index or the vendor index?

Is there a way to instruct mongodb to prefer a certain index over another, for a given query?

Tags:

2

Answers


  1. 0

    Their official website states:

    MongoDB uses multikey indexes to index the content stored in arrays. If you index a field that holds an array value, MongoDB creates separate index entries for every element of the array. These multikey indexes allow queries to select documents that contain arrays by matching on element or elements of the arrays.

    You can checkout This article for more information

    For your second query, you can try creating custom indexes for documents. Checkout their Documentation for the same

    Login or Signup to reply.
  2. 0

    When a certain query is done on a mongodb collection, if there are
    multiple indexes that can be used to perform the query, how does
    mongodb choose the index for the query?

    You can generate a query plan for a query you are trying to analyze – see what indexes are used and how they are used. Use the explain method for this; e.g. db.collection.explain().find(). The explain takes a parameter with values "queryPlanner" (the default), "executionStats" and "allPlansExecution". Each of these have different plan output.

    The query optimizer generates plans for all the indexes that could be used for a given query. In your example order collection, the two single field indexes (one each for the fields customer and vendor) are possible candidates (for a query filter with both the fields). The optimizer uses each of the plans and executes them for a certain period of time and chooses the best performing candidate (this is determined based upon factors like – which returned most documents in least time, and other factors). Based upon this it will output the winning and rejected plans and these can be viewed in the plan output. You will see one of the indexes in the winning plan and the other in the rejected plan in the output.

    MongoDB caches the plans for a given query shape. Query plans are cached so that plans need not be generated and compared against each other every time a query is executed.

    Is there a way to instruct mongodb to prefer a certain index over
    another, for a given query?

    There are couple of ways you can use:

    1. Force MongoDB to use a specific index using the hint() method.
    2. Set Index Filters to specify which indexes the optimizer will evaluate for a query shape. Note that this setting is not persisted after a server shutdown.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search