First, select a practice database and insert sample employee records.

use performanceDB

db.employees.insertMany([
    {
        employee_id: 1001,
        name: "Rahul",
        department: "IT",
        salary: 45000,
        city: "Patna"
    },
    {
        employee_id: 1002,
        name: "Priya",
        department: "HR",
        salary: 35000,
        city: "Gaya"
    },
    {
        employee_id: 1003,
        name: "Amit",
        department: "IT",
        salary: 55000,
        city: "Patna"
    },
    {
        employee_id: 1004,
        name: "Neha",
        department: "Finance",
        salary: 40000,
        city: "Delhi"
    },
    {
        employee_id: 1005,
        name: "Ravi",
        department: "IT",
        salary: 60000,
        city: "Gaya"
    }
])

Run the insertion only once. Repeated execution adds duplicate sample records.

These five documents are sufficient to demonstrate syntax, but meaningful performance comparisons require a larger dataset.

An index is a data structure that helps MongoDB locate matching documents without examining every document for many query patterns.

Without a suitable index, a query may perform a collection scan.

db.employees.find({
    department: "IT"
})

Create an index on the department field:

db.employees.createIndex({
    department: 1
})

The value 1 specifies ascending index order. Use -1 for descending order.

View the indexes on a collection:

db.employees.getIndexes()

Indexes can improve reads, but they consume storage and add overhead to inserts, updates, and deletes. Create indexes based on actual query patterns.

A unique index prevents multiple documents from having the same indexed value.

db.employees.createIndex(
    { employee_id: 1 },
    { unique: true }
)

Now try inserting a duplicate employee ID:

db.employees.insertOne({
    employee_id: 1001,
    name: "Duplicate Employee",
    department: "IT",
    salary: 30000,
    city: "Patna"
})

If employee ID 1001 already exists, MongoDB rejects this insertion because of the unique index.

Important: if duplicate values already exist, creating the unique index will fail until the duplicates are resolved. Existing applications should check their data before adding a unique index.

The explain() method shows how MongoDB plans and executes a query.

db.employees.find({
    department: "IT"
}).explain("executionStats")

Look for these fields:

• nReturned — number of documents returned.
• totalDocsExamined — number of documents examined.
• totalKeysExamined — number of index keys examined.
• executionTimeMillis — reported execution time in milliseconds.
• winningPlan — the selected query plan.

Example of analyzing a query:

db.employees.find({
    department: "IT"
}).explain("executionStats")

A plan containing COLLSCAN indicates a collection scan. A plan containing IXSCAN indicates index scanning. A collection scan is not always bad: for small collections or queries returning many documents, it can be reasonable.

Compare execution statistics before and after creating an appropriate index. Do not judge performance from a single timing measurement on a tiny dataset.

Projection returns only the fields required by the application, reducing unnecessary data transfer.

Less specific query:

db.employees.find({
    department: "IT"
})

Query with projection:

db.employees.find(
    { department: "IT" },
    {
        _id: 0,
        name: 1,
        department: 1,
        salary: 1
    }
)

This query returns only the name, department, and salary fields. The _id: 0 setting excludes the document ID.

Projection can reduce network traffic and application processing. It does not automatically guarantee a faster database query.

Consider sorting employees by salary:

db.employees.find().sort({
    salary: -1
})

Create an index that supports the sort:

db.employees.createIndex({
    salary: -1
})

Analyze the query:

db.employees.find().sort({
    salary: -1
}).explain("executionStats")

A suitable index may allow MongoDB to return documents in the requested order without a separate in-memory sort. The planner decides whether using the index is beneficial.

If a query filters by department and sorts by salary, consider a compound index such as:

db.employees.createIndex({
    department: 1,
    salary: -1
})

Compound index field order matters. This index can support queries that filter on department and sort matching employees by salary.

Return only the first ten employees:

db.employees.find()
    .limit(10)

Skip the first ten documents and return the next ten:

db.employees.find()
    .sort({ employee_id: 1 })
    .skip(10)
    .limit(10)

Using a consistent sort helps keep page results predictable. For large offsets, however, skip() can become expensive because MongoDB must work through the skipped results.

For large collections, consider range-based pagination using an indexed field.

// Example: continue after employee ID 1010
db.employees.find({
    employee_id: { $gt: 1010 }
}).sort({
    employee_id: 1
}).limit(10)

This range-based example works best when employee_id is indexed and unique. Store the last employee ID from the previous page and use it for the next request.

Aggregation pipelines process documents through multiple stages. Filtering early can reduce the number of documents processed by later stages.

db.employees.aggregate([
    {
        $match: {
            department: "IT"
        }
    },
    {
        $group: {
            _id: "$department",
            totalEmployees: {
                $sum: 1
            },
            averageSalary: {
                $avg: "$salary"
            }
        }
    },
    {
        $sort: {
            averageSalary: -1
        }
    }
])

Recommended practices:

• Use an early $match when applicable.
• Use $project when removing unnecessary fields is useful.
• Avoid unnecessarily large intermediate results.
• Review the pipeline with explain() when investigating performance.
• Index fields used by selective filters when appropriate.

MongoDB can automatically optimize some pipeline operations, so measure the actual plan instead of assuming that manually rearranging every stage will improve performance.

A compound index contains multiple fields and can support queries involving those fields.

db.employees.createIndex({
    department: 1,
    city: 1,
    salary: -1
})

Example query:

db.employees.find({
    department: "IT",
    city: "Patna"
}).sort({
    salary: -1
})

The index follows the field order department, city, and salary. Compound indexes generally support queries on their leading fields, subject to the query and sort pattern.

Avoid creating a large number of overlapping indexes. Review real application queries before choosing compound index fields and order.

MongoDB provides diagnostic tools for identifying queries that take longer than expected.

Inspect the current database profiler status:

db.getProfilingStatus()

For a controlled development environment, profiling can be enabled for operations exceeding a threshold.

// Example: profile operations slower than 100 ms
db.setProfilingLevel(1, {
    slowms: 100
})

Inspect recent profiler records:

db.system.profile.find()
    .sort({ ts: -1 })
    .limit(10)

Disable profiling when you no longer need it:

db.setProfilingLevel(0)

Important: database profiling adds overhead and may record query details. Use it carefully, particularly on production systems. Review your MongoDB version and deployment configuration before changing profiling settings.

Get statistics for the current database:

db.stats()

Get statistics for a specific collection:

db.employees.stats()

Check the current server status:

db.serverStatus()

View collection indexes:

db.employees.getIndexes()

These commands provide information about storage, indexes, connections, and server activity. Some server statistics require suitable privileges. For a production deployment, also use your monitoring system and review CPU, memory, disk latency, connection counts, and query latency.

List the collection's indexes first:

db.employees.getIndexes()

Drop a specific non-essential index by name:

db.employees.dropIndex("salary_-1")

The index name shown here is the usual name generated for an index on { salary: -1 }. Confirm the actual name using getIndexes() before dropping an index.

Do not drop indexes that are required for application queries, unique constraints, or other business rules. MongoDB does not allow dropping the collection's mandatory _id index.

1. Create a database named performanceDB.
2. Insert at least 1,000 employee documents for realistic testing.
3. Create an index on the department field.
4. Compare a query plan before and after creating an index.
5. Use explain("executionStats") to inspect query execution.
6. Return only the required fields using projection.
7. Create a compound index for department and salary.
8. Test sorting with and without an appropriate index.
9. Compare offset pagination with range-based pagination.
10. Optimize an aggregation pipeline with a selective filter.
11. Inspect database and collection statistics.
12. Review existing indexes and identify any unnecessary ones.

Record the execution statistics from your tests. Performance depends on dataset size, hardware, indexes, cache state, query selectivity, and the MongoDB deployment.