Single indexes

In MongoDB, single-field indexing refers to creating an index on a single field of the documents in a collection. Indexing improves the performance of search queries by allowing the database engine to look up values more efficiently. The most basic type of index is the single-field index, and MongoDB automatically creates one on the _id field when you create a new collection.

Creating a Single-Field Index

To create a single-field index, you can use the createIndex method. For example, to create an index on the username field of a users collection, you would do:

db.users.createIndex({ "username": 1 })

Here, 1 indicates that the index is in ascending order. You can use -1 for descending order.

Types of Single-Field Indexes

1. Ascending Index (1): Sorts the index in ascending order.
2. Descending Index (-1): Sorts the index in descending order.

Querying with Single-Field Index

Once the index is created, MongoDB will automatically use it for queries that can benefit from it. For example:

db.users.find({ "username": "john_doe" })

This query would utilize the index on the username field, making the search operation faster.

Explain Plan

You can use the explain method to see if your query is using the index:

db.users.find({ "username": "john_doe" }).explain("executionStats")

This will provide detailed information about the query execution, including whether it used an index.

Considerations

Understanding when to use and when not to use single-field indexes is crucial for optimizing MongoDB performance. Below are some scenarios that illustrate the appropriate and inappropriate use of single-field indexes.

When to Use Single-Field Indexes

1. High Cardinality Fields: When a field has a large number of unique values, a single-field index can significantly speed up queries.

   Example: Searching for users by their unique usernames.

   // Create an index on the username field
   db.users.createIndex({ "username": 1 })
   
   // Query to find a user by username
   db.users.find({ "username": "john_doe" })

2. Frequent Queries: If a particular field is often queried, it's a good candidate for indexing.

   Example: Finding products by their category.

   // Create an index on the category field
   db.products.createIndex({ "category": 1 })
   
   // Query to find products in a specific category
   db.products.find({ "category": "Electronics" })

3. Sorting: If you frequently need to sort query results by a particular field, a single-field index can help.

   Example: Sorting blog posts by their creation date.

   // Create an index on the creationDate field
   db.posts.createIndex({ "creationDate": 1 })
   
   // Query to find posts sorted by creation date
   db.posts.find().sort({ "creationDate": -1 })

When Not to Use Single-Field Indexes

1. Low Cardinality Fields: Fields with low cardinality (i.e., a small number of unique values) are generally not good candidates for single-field indexes.

   Example: A gender field that only contains "Male" or "Female" values.

   // Not recommended
   db.users.createIndex({ "gender": 1 })

2. Infrequent Queries: If a field is rarely queried, the overhead of maintaining the index might not be worth the occasional performance gain.

   Example: An archiveDate field that is only used in yearly audits.

   // Not recommended
   db.documents.createIndex({ "archiveDate": 1 })

3. Write-Heavy Workloads: If your application performs many more write operations compared to read operations, the overhead of maintaining indexes can impact performance.

   Example: A logging system that writes logs to the database but rarely reads them.

   // Not recommended
   db.logs.createIndex({ "timestamp": 1 })

4. Compound Queries: For queries involving multiple fields, a single-field index may not be sufficient, and a compound index would be more appropriate.

   Example: Searching for users based on both their age and location.

   // Single-field indexes may not be efficient
   db.users.find({ "age": { "$gt": 21 }, "location": "New York" })