Automated Local Secondary Index Generation for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Document databases often rely solely on primary keys for efficient data lookup, which may not suffice for all querying needs, particularly in organizations with vast data sets like pharmaceutical companies generating reports, necessitating additional search capabilities through secondary indexes.
Innovation Solution
A method and system for automatically generating and updating local secondary indexes based on usage factors, which include receiving raw data, generating base tables with primary and sort keys, and developing local secondary indexes with specific sort keys, optimizing query efficiency over time based on user interaction data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If only primary keys are used for data lookup, then the database structure remains simple, but query efficiency deteriorates for complex searching needs
Solution Approach 1:
The patent segments the indexing function by creating multiple types of secondary indexes (local secondary indexes and global secondary indexes) that are separate from the primary key structure. This allows the database to maintain a simple primary key while adding specialized index structures for different query patterns, thus improving query efficiency without fundamentally complicating the core database structure.
Solution Approach 2:
The patent adds another dimension to the indexing system by introducing secondary indexes that operate independently from the primary key. These secondary indexes provide additional lookup paths based on different attributes, enabling efficient queries on non-primary-key fields without affecting the simplicity of the primary key structure.
2Speed
If secondary indexes are manually created, then query capabilities improve, but system complexity and maintenance burden increase
Solution Approach 1:
The patent implements self-service through automated index management where the system automatically creates, updates, and manages secondary indexes based on observed query patterns. This eliminates the need for manual index creation and maintenance by database administrators, reducing system complexity while maintaining improved query capabilities through dynamically adapted indexes.
Solution Approach 2:
The system incorporates feedback mechanisms that monitor query patterns and automatically adjust the secondary index structure accordingly. By continuously analyzing how data is accessed and dynamically creating or modifying indexes based on this feedback, the system optimizes query performance automatically without requiring manual intervention or increasing operational complexity.
3Speed
If static secondary indexes are used, then initial query performance improves, but adaptability to changing query patterns deteriorates
Solution Approach 1:
The patent applies dynamics by making secondary indexes adaptive rather than static. The system continuously monitors query patterns and automatically creates or modifies secondary indexes based on changing access patterns. This dynamic approach allows the database to maintain optimal query performance as query patterns evolve over time, combining the benefits of indexed queries with adaptability to new requirements.
Data Source
AI summary
A method of querying a set of raw data includes: receiving raw data; generating a base table having a primary key including a partition key and a sort key; generating a first set of local secondary indexes for the base table, the specific local secondary index sort key being a different one of the plurality of attributes than the sort key of the base table; developing one or more usage factors for each of the one or more local secondary indexes of the base table; receiving a second set of raw data having a plurality of attributes; generating a second base table including the second set of raw data; and generating a second set of local secondary indexes for the second base table based on the usage factors associated with the set of local secondary indexes.