Monghoul
Esc

    Fix a slow query with an index

    Go from a query that feels slow to the index that fixes it, using the explain view and the profiler.

    Checked against v1.11.0 Updated
    The explain view grading a query that runs a collection scan, with an index suggestion
    Fig 01 Explain · A COLLSCAN graded, with the index that would fix it

    1. Confirm it is the query

    Auto-explain collects the plan for you, so the result reports its scan type without you asking for it. Monghoul gets it by running the read a second time, which is why it is skipped for anything that could write. If it used an index and the query is still slow, the plan is graded indexed rather than optimal: the index exists but is not selective, and step 3 still applies.

    2. Read the plan

    Switch the result to Explain. The number that matters is documents examined against documents returned. Examining 1.8 million to return 40 is under 0.1 percent efficiency, and that is the shape of query that works on a laptop and fails in production two quarters later.

    Watch for a sort that happens in memory. A query can use an index to find its documents and still sort them afterwards, and an in-memory sort has a server-side limit it will eventually hit.

    3. Take the suggestion, then read it

    When an index would remove the scan, Explain writes the createIndex call and offers a Create button. The fields are already ordered by the ESR rule, which is equality first, then sort, then range.

    Read it before you accept it. The suggestion comes from this one query, and every index costs write throughput and disk on every insert.

    4. Check what you already have

    Open the collection’s Indexes. Each one carries its size and its usage count from $indexStats, coloured green when it is being used and muted when it is not, with the time usage tracking started.

    An unused index on a write-heavy collection is pure cost. This is the screen that tells you which ones to drop, and dropping one is usually a bigger win than adding another.

    5. Find the ones you have not noticed

    The Cluster Monitor’s Operations view has a Profiler mode. Set a database to slow-only, let it run, and it reports the 200 most recent profiled operations with collection summaries. That finds the queries nobody is watching, which are the ones that page you.

    Profiling is a server setting with a cost. Turn it off when you are done.

    Next

    The create index dialog with key fields, options, and a partial filter expression
    Fig 02 Indexes · Keys, options, and a partial filter on one form