CODEWITHSUNDEEP
mongodbindexing
Manuel
Manuel

Reputation: 15042

How to see which queries are using an index in MongoDB?

I see that an index is being used by MongoDB but I am not sure which queries are using it.

How can I see which queries are using the index?

Edit: To clarify, I want to see by which queries an index is used, not the other way around using explain to see which indices are used by a specific query.

Upvotes: 6

Views: 3498

Answers (3)

KushalSeth
KushalSeth

Reputation: 4629

In heavy databases, indexes plays an important role.

So, you can run the query by adding .explain("allPlansExecution") to your query or clicking on Explain in NoSQLBooster as shown below.

Run your query with .explain("allPlansExecution"): Something like this:

db.userdata.find({ "User.CountryID" : 10 })
   .projection({})
   //.sort({_id:-1})
   .limit(10)
   .explain("allPlansExecution")

This .explain("allPlansExecution") explains you if Index is being used.

I have explained the JSON as well as the graphical view:

1. If you want to check the JSON, then:

  • Run the query with .explain("allPlansExecution"), you will get the JSON. If you can see stage as IXSCAN the your particular index is being used. but if you are seeing stage as COLSCAN (Collection Scan), then index is not used.

    JSON comparison for indexed query and non indexed query: enter image description here

  • Understanding the Schema: (official link) The basic JSON structure of the .explain("allPlansExecution") response is something like this:

    enter image description here

    queryPlanner: MongoDB runs the query optimizer to choose the winning plan for the operation under evaluation. queryPlanner explains, which details the query optimizer has selected plan has selected and rejected.

    executionStats: MongoDB runs the query optimizer to choose the winning plan, executes the winning plan to completion, and returns statistics describing the execution of the winning plan.

2. If you are using NoSQLBooster for mongoDB. then just write your query and click on explain, it will show which indexes are being used.

Index Not Used: enter image description here

Index Used: enter image description here

Upvotes: 2

Sven
Sven

Reputation: 886

If you have access to the server then one way to get an idea about queries not using indexes is to look at mongodb log file.

Grep "COLLSCAN" from the logs and you get the idea. If indexes are used then the query plan shows "IXSCAN". If it has to scan collection then it shows "COLLSCAN". I've found some slow queries this way. Another symptom of queries not using indexes properly is constant high CPU load on the database server.

Upvotes: 1

Ravi Shankar Bharti
Ravi Shankar Bharti

Reputation: 9268

You can run your query with .explain() to find out which index is being used in that particular query.

Eg. with .find() you can use .explain() like this : 

db.collection.find().explain()

For more information read MongoDB Explain

Edit : Check index used in logs

To check the which indices are being used in a particular query, you can enable index level logs in mongodb configuration.

From official docs

INDEX

Messages related to indexing operations, such as creating indexes. To specify the log level for INDEX components, set the systemLog.component.index.verbosity parameter.

Enable systemLog.component.index.verbosity parameter to enable index logs.

From docs :

systemLog.component.index.verbosity Type: integer

Default: 0

New in version 3.0.

The log message verbosity level for components related to indexing operations. See INDEX components.

The verbosity level can range from 0 to 5:

0 is the MongoDB’s default log verbosity level, to include Informational messages. 1 to 5 increases the verbosity level to include Debug messages.

Read MongoDB configuration options - Index verbosity for more info.

Upvotes: 2

Related Questions