Index Table Sorting for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query processing systems lack efficiency in executing queries across multiple index tables, leading to suboptimal performance due to unsorted index table usage and inflexible query specification, especially under varying database loads.
Innovation Solution
A computer system that sorts index table identifiers based on predefined or dynamically updated sequences, allowing for flexible query specification through data structures and mapping tables, and optimizing query execution by prioritizing relevant index tables, with the ability to update sequences according to database load and include custom data fields without modifying the programming.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If index tables are used to speed up query execution, then reading speed is improved, but query execution efficiency deteriorates when multiple unsorted index tables are processed
Solution Approach 1:
The system pre-sorts index table identifiers according to a predefined sequence before query execution. This preliminary sorting action ensures that when queries need to access multiple index tables, they do so in an optimized order, eliminating the need to sort during query execution and thereby improving query execution efficiency while maintaining the speed benefit of using index tables.
2Device complexity
If the system uses a fixed query processing approach, then implementation is simple, but adaptability to varying database loads deteriorates
Solution Approach 1:
The system dynamically adapts its query execution strategy based on database load conditions. When database load is high, the system adjusts the predefined sequence of index tables to optimize performance under varying conditions. This dynamic adjustment mechanism allows the system to maintain simplicity in implementation while gaining adaptability to different database load scenarios.
3Adaptability or versatility
If custom data fields are added to queries, then query flexibility is improved, but system complexity increases requiring programming modifications
Solution Approach 1:
The system implements a universal query processing mechanism that can handle both standard and custom data fields through the same predefined sequence framework. By designing the system to work with data field names as identifiers in the predefined sequence, custom data fields can be added without requiring programming modifications - the system automatically adapts to handle them through the existing universal processing path.
Data Source
AI summary
A computer system including a database having a database table for storing records including first columns for storing data values, and at least one second column for storing keys identifying records stored in the database. The computer system also including a set of index tables with each having assigned thereto an index table identifier, an arrangement for receiving a query an arrangement for storing a predefined ordered sequence of index table identifiers, and an arrangement for processing the query by checking each one of the index tables for being relevant for the execution of the query, storing the index table identifier for each relevant index table in a query execution table, sorting the query execution table in accordance with the predefined ordered sequence, and executing the query using the index tables identified in the query execution table in the order given by the sorting of the query execution table.