Multi-column Index Selection via Summary Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The process of manually selecting indexes in database design for improving query performance is difficult and prone to mistakes, especially when dealing with multi-column indexes, due to the high resource overhead and time required for collecting statistics, which can lead to inefficient index recommendations that consume disk space and incur maintenance overhead.
Innovation Solution
An efficient method is introduced that generates summary statistics for multi-column index candidates from single column statistics, followed by detailed statistics collection on leading candidates, using a genetic algorithm to identify the best index configurations and reduce the number of candidates considered, thereby minimizing resource usage and improving recommendation quality.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If detailed statistics are collected on all multi-column index candidates, then index recommendation accuracy is improved, but resource overhead and elapsed time increase significantly
Solution Approach 1:
The patent segments the statistics collection process into two distinct phases: (1) collecting detailed statistics only on a limited set of leading candidates identified through preliminary analysis, and (2) using summary statistics for remaining candidates. This segmentation reduces the total statistics collection time while maintaining recommendation accuracy by focusing computational resources on the most promising index candidates.
Solution Approach 2:
The patent applies partial action by collecting detailed statistics on only the leading candidates (a subset of all possible index candidates) rather than all candidates. The system uses summary statistics for non-leading candidates, which is sufficient for the evaluation process. This partial collection approach reduces resource overhead while maintaining adequate recommendation quality.
2Measurement precision
If statistics are collected on all multi-column index candidates, then recommendation quality is improved, but device complexity and operational difficulty increase
Solution Approach 1:
The patent divides the complex statistics collection process into manageable segments: identifying leading candidates using preliminary criteria, collecting detailed statistics only on those leaders, and using summary statistics for others. This segmentation simplifies the overall process by reducing the number of detailed statistics collections required while maintaining recommendation quality.
Solution Approach 2:
The patent introduces summary statistics as an intermediary mechanism that bridges the gap between detailed statistics and the evaluation process. Summary statistics allow the system to evaluate non-leading candidates without requiring full detailed statistics collection, thereby simplifying the overall process while maintaining adequate recommendation quality.
3Ease of operation
If manual index selection is performed, then control over index design is improved, but productivity and efficiency decrease
Solution Approach 1:
The patent implements an automated index selection system that performs statistics collection, candidate evaluation, and recommendation generation without requiring manual intervention. The system self-services by automatically identifying leading candidates, collecting necessary statistics, and producing index recommendations, thereby significantly improving productivity while reducing the burden on database administrators.
Solution Approach 2:
The automated system incorporates feedback mechanisms where query execution plans and cost estimates are continuously evaluated to identify leading index candidates. This feedback loop enables the system to adapt to actual workload patterns and optimize index recommendations dynamically, improving both efficiency and relevance of recommendations.
Data Source
AI summary
In an example embodiment, a method is illustrated to determine multi-column indexes, the method including receiving one or more structured query language (SQL) queries, the one or more SQL queries defining a workload. A database index analysis tool may be used to determine a plurality of index configurations for executing the workload. Further, leading index configurations may be determined, leading index configurations including a subset of the plurality of index configurations. Also, a statistics database may be updated with complete statistics for the leading index configurations.