Index attributes (unique index, sparse index, partial index)

Indexes are the core mechanism in MongoDB for optimizing query performance, and different types of indexes can achieve efficient data retrieval for specific scenarios. Index properties include uniqueness, sparsity, and partiality, which are used to enforce data uniqueness, skip null-valued fields, and enable conditional filtering, respectively.

Unique Indexes

Unique indexes ensure that the indexed field values in a collection are not duplicated, similar to the UNIQUE constraint in relational databases. When inserting or modifying documents results in duplicate indexed field values, MongoDB throws an E11000 duplicate key error.

// Example of creating a unique index
db.users.createIndex({ "email": 1 }, { unique: true })

// Inserting conflicting data will throw an error
db.users.insertMany([
  { email: "user1@example.com", name: "Alice" },
  { email: "user1@example.com", name: "Bob" }  // Throws a duplicate key error
])

Special cases for unique indexes include:

1. Allowing multiple null values unless combined with a sparse index
2. Compound unique indexes require the combination of values to be unique, while individual fields can be duplicated
3. In sharded collections, the shard key inherently has uniqueness

// Example of a compound unique index
db.products.createIndex(
  { "category": 1, "sku": 1 }, 
  { unique: true }
)

// The following insert operations are allowed
db.products.insertMany([
  { category: "electronics", sku: "A001" },
  { category: "furniture", sku: "A001" },  // Different combination of values
  { category: "electronics", sku: null }   // Null values can be duplicated
])

Sparse Indexes

Sparse indexes only include entries for documents that have the indexed field, skipping documents where the field is missing or null. This type of index is particularly suitable for optional fields, significantly reducing index storage space.

// Creating a sparse index
db.profiles.createIndex(
  { "social_media.twitter": 1 },
  { sparse: true }
)

// Only the first two documents will be indexed
db.profiles.insertMany([
  { name: "A", "social_media": { twitter: "@a" } },
  { name: "B", "social_media": { twitter: "@b" } },
  { name: "C", "social_media": { facebook: "c" } },  // No twitter field
  { name: "D" }                                      // No social_media field
])

Typical use cases for sparse indexes:

• Optional information in user profiles (e.g., birthday, address)
• Non-mandatory fields in progressive forms
• Type-specific fields in polymorphic document structures

// Example of a sparse compound index
db.contacts.createIndex(
  { "phone": 1, "email": 1 },
  { sparse: true }
)

// Query optimization: Using a sparse index to accelerate existence checks
db.contacts.find({
  phone: { $exists: true },
  email: { $exists: true }
}).explain("executionStats")

Partial Indexes

Partial indexes only index documents that meet specified filter conditions, reducing storage and maintenance costs compared to full-collection indexes. These indexes are defined using the partialFilterExpression option.

// Creating a partial index: Only index documents with status "active"
db.orders.createIndex(
  { "order_date": 1 },
  { 
    partialFilterExpression: { 
      status: "active",
      total: { $gt: 100 }
    }
  }
)

// The following query will use this partial index
db.orders.find({
  status: "active",
  total: { $gt: 150 },
  order_date: { $gt: ISODate("2023-01-01") }
})

Key considerations for partial index design:

1. Filter conditions should use highly selective fields
2. Can be combined with sparse index properties
3. Queries must include the filter condition or a subset to utilize the index

// Partial index with complex conditions
db.logs.createIndex(
  { "timestamp": -1 },
  {
    partialFilterExpression: {
      $or: [
        { severity: "ERROR" },
        { response_time: { $gt: 1000 } }
      ]
    }
  }
)

// Example of an equivalent query
db.logs.find({
  timestamp: { $gt: ISODate("2023-06-01") },
  $or: [
    { severity: "ERROR" },
    { response_time: { $gt: 1200 } }
  ]
})

Combining Index Properties

In real-world scenarios, it is common to combine multiple index properties. For example, creating an index with both uniqueness and sparsity can resolve the issue of duplicate null values.

// Combining unique and sparse indexes
db.customers.createIndex(
  { "tax_id": 1 },
  { 
    unique: true,
    sparse: true 
  }
)

// The following insert operations are allowed
db.customers.insertMany([
  { name: "A", tax_id: "123" },
  { name: "B" },                // No tax_id field
  { name: "C", tax_id: null },  // Null value
  { name: "D" }                 // No tax_id field
])

// However, this operation will fail due to duplication
db.customers.insertOne({ name: "E", tax_id: "123" })

Performance optimization case: E-commerce product collection

// Compound index design
db.products.createIndexes([
  // Unique index: Product code
  {
    "product_code": 1,
    "vendor_id": 1
  },
  { unique: true },
  
  // Partial index: Best-selling products
  {
    "category": 1,
    "sales_count": -1
  },
  {
    partialFilterExpression: {
      sales_count: { $gt: 1000 },
      stock: { $gt: 0 }
    }
  },
  
  // Sparse index: Optional attributes
  {
    "specifications.weight": 1
  },
  { sparse: true }
])

Index Maintenance Strategies

After creating indexes, regular monitoring and maintenance are required:

// View index usage statistics
db.collection.aggregate([
  { $indexStats: {} }
])

// Example of index rebuilding
db.runCommand({
  compact: "collection",
  force: true,
  index: { name: "index_name" }
})

Common issue handling:

1. For unique index conflicts, use writeConcern to control behavior
2. Ensure the query planner correctly selects partial indexes
3. Sparse indexes may lead to inconsistent query results