Database Index Selection Using Compressed Workload Clustering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Selecting optimal indexes in large database management systems is computationally intensive due to the complexity of analyzing large workloads of SQL queries, which hinders efficient database performance.
Innovation Solution
A method is introduced to create a compressed workload by identifying and selecting the most costly queries, forming a subset of predicates based on their execution costs, and using this compressed workload to perform index selection, thereby reducing the complexity of the index selection process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If index selection is performed based on a large workload containing all logged database queries, then the accuracy of index selection is improved, but the computational intensity increases significantly
Solution Approach 1:
The patent extracts a representative subset of predicates from the complete workload using clustering algorithms. Instead of analyzing all predicates in the large workload, the system identifies and extracts a smaller set of representative predicates that capture the essential patterns and characteristics of the workload, thereby reducing computational intensity while maintaining index selection accuracy.
Solution Approach 2:
The patent creates a compressed workload that is a simplified copy of the original workload. By using clustering to generate representative predicates and constructing a compressed workload from these representatives, the system maintains the essential characteristics of the full workload in a reduced form that requires significantly less computational resources to analyze.
2Manufacturing precision
If the complete workload is analyzed in detail, then the quality of index selection results is improved, but the time required for index selection increases
Solution Approach 1:
The patent segments the large workload into multiple clusters using clustering algorithms. By dividing the complete set of predicates into representative groups, the system can analyze a smaller set of cluster representatives instead of every individual predicate, significantly reducing index selection time while preserving the quality of results through the representative nature of the clusters.
Solution Approach 2:
The patent performs partial analysis by focusing on a compressed subset of the workload rather than the complete workload. The clustering-based compression creates a reduced workload that contains sufficient information to produce high-quality index selection results without requiring exhaustive analysis of every predicate in the original large workload.
3Adaptability or versatility
If all predicates in the workload are examined, then comprehensive index coverage is achieved, but the complexity of the analysis process increases
Solution Approach 1:
The patent extracts representative predicates from the complete workload using clustering techniques. By identifying and extracting a smaller set of representative predicates that capture the diversity and characteristics of the full workload, the system achieves comprehensive index coverage through the representatives without requiring analysis of every individual predicate, thereby reducing analysis complexity.
Solution Approach 2:
The patent transforms the workload representation by changing parameters through clustering. Instead of working with the original large set of predicates, the system transforms the data into cluster centers or representatives, changing the parameter space from individual predicates to clustered representations, which reduces complexity while maintaining coverage.
Data Source
AI summary
A first workload that contains logged predicates contained in database queries is received. A subset of the predicates is selected based on costs associated with the predicates. A compressed workload is formed using the selected subset of the predicates. Indexes are then selected based on the compressed workload.