Automatic Database Index Creation Using Workload and Resource Criteria

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional database systems face challenges in managing index creation, leading to performance degradation due to reactive indexing that consumes system resources and may not benefit future searches, particularly in distributed database deployments.

Innovation Solution

An auto-index system that determines candidate indexes based on predefined criteria, including CPU utilization, cluster load, and replication health, to automatically create indexes without user intervention, ensuring minimal performance impact.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Speed

If indexes are created reactively to improve query performance, then query speed is improved, but system resources are consumed and performance degradation occurs

Engineering Contradiction:

Improvequery speedVSAvoidsystem resource consumption

Core Design Contradiction:

SpeedVSLoss of energy

Solution Approach 1:

The system proactively identifies and creates indexes before they are actually needed by analyzing query patterns and performance metrics. This preliminary action prevents the reactive creation of indexes that would consume system resources, as indexes are created only when the system determines they will provide genuine benefit based on pre-established criteria.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system continuously monitors query performance, index usage, and system resource utilization to dynamically adjust index creation decisions. This feedback mechanism ensures that indexes are created only when performance benefits outweigh resource costs, preventing unnecessary resource consumption while maintaining query speed improvements.

Inventive Principle:

Principle #23Feedback

2Adaptability or versatility

If multiple indexes are created to handle diverse query patterns, then query flexibility is improved, but system complexity and maintenance burden increase

Engineering Contradiction:

Improvequery flexibilityVSAvoidsystem complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The system dynamically adjusts index creation parameters such as selection criteria, resource thresholds, and priority weighting based on changing workload patterns and system conditions. This allows the system to adapt to diverse query patterns without creating unnecessary indexes, maintaining query flexibility while controlling system complexity through parameter optimization.

Inventive Principle:

Principle #35Parameter changes

3Ease of operation

If automatic index creation is enabled to reduce manual intervention, then ease of operation is improved, but risk of creating inappropriate indexes increases

Engineering Contradiction:

Improveease of operationVSAvoidindex creation accuracy

Core Design Contradiction:

Ease of operationVSReliability

Solution Approach 1:

The system autonomously monitors its own performance metrics, identifies optimization opportunities, and creates indexes based on pre-configured criteria without requiring manual intervention. This self-service approach maintains ease of operation while ensuring reliability through consistent application of validated index creation rules and continuous performance verification.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system continuously evaluates the effectiveness of automatically created indexes and adjusts future creation decisions based on actual performance outcomes. This feedback loop ensures that automatic index creation maintains high reliability by creating indexes only when they demonstrably improve performance and meet established criteria.

Inventive Principle:

Principle #23Feedback

4Loss of time

If index creation is performed immediately when needed, then response time is improved, but system performance degradation occurs due to resource consumption

Engineering Contradiction:

Improveresponse timeVSAvoidsystem performance

Core Design Contradiction:

Loss of timeVSProductivity

Solution Approach 1:

The system performs preliminary analysis of query patterns and pre-identifies candidate indexes that would benefit performance. This allows the system to create indexes proactively during low-utilization periods rather than immediately when needed, reducing response time for future queries while avoiding performance degradation during index creation by scheduling it appropriately.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system creates indexes periodically based on monitored performance thresholds and system utilization levels rather than immediately when a performance issue is detected. This periodic approach allows index creation to occur during optimal windows when system resources are available, maintaining fast response times while preventing performance degradation during the index build process.

Inventive Principle:

Principle #19Periodic action

Data Source

PatentUS12493595B2Systems and methods for automatic index creation in database deployment

Publication Date: 2025.12.09 MONGODB INC

AI summary

In some embodiments, an auto-index system may be provided to determine one or more candidate indexes to be automatically created in a database system. The auto-index system may determine if one or more criteria associated with creating the one or more candidate indexes are met. In response to determining that the one or more criteria associated with creating the one or more candidate indexes are met, the auto-index system may automatically create the one or more candidate indexes, for example, by an automation agent. The one or more criteria may also be associated with respective performance metrics of the database system.