Composite Index Ordering for Data Visualization Filter Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Data visualization applications face performance degradation due to inefficient index creation, as they struggle to automatically generate composite indexes for all possible filter combinations, leading to increased storage overload and query execution times.
Innovation Solution
A method is implemented where a data visualization application determines visualization and non-visualization columns, generates a composite index by ordering all columns with visualization columns first, and uses unique values for non-visualization columns to optimize queries by including NOT filters, thereby reducing storage and improving query efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If a composite index is created on all columns to support all possible filter combinations, then query execution speed is improved, but storage space is excessively consumed and system complexity increases
Solution Approach 1:
The patent segments the index creation process by identifying and separating visualization columns from non-visualization columns. Instead of creating indexes on all columns, it creates composite indexes only on visualization columns and simple indexes only on non-visualization columns when filters are applied. This segmentation reduces the total number of indexes created, thereby reducing storage space consumption while maintaining query performance for visualizations.
Solution Approach 2:
The patent applies different indexing strategies to different sets of columns based on their specific needs. Visualization columns receive composite indexes optimized for aggregation and filtering operations, while non-visualization columns receive simple indexes only when actually filtered. This local quality approach ensures that indexing resources are allocated efficiently to where they are most needed, rather than uniformly across all columns.
2Speed
If indexes are created for all possible filter combinations, then query performance is improved, but device complexity and maintenance overhead increase
Solution Approach 1:
The patent implements a dynamic indexing strategy where the system determines at runtime which non-visualization columns are actually filtered and creates or uses indexes only for those specific columns. This dynamic approach avoids the need to pre-create and manage indexes for all possible filter combinations, significantly reducing index management complexity while maintaining optimal query performance for actual use cases.
Solution Approach 2:
The system automatically determines which indexes are needed based on the actual filter conditions applied to non-visualization columns, eliminating the need for manual index creation and management. The database engine self-adapts to the specific query requirements by utilizing the composite index on visualization columns and selectively applying simple indexes on filtered non-visualization columns, reducing the burden on database administrators.
3Speed
If a simple index is created on one data column, then query operations on that column are optimized, but queries involving multiple columns or filters on non-indexed columns suffer performance degradation
Solution Approach 1:
The patent merges multiple indexing strategies into a unified approach. It creates composite indexes that combine visualization columns with selected non-visualization columns based on actual filter conditions. This merging allows the system to handle both single-column queries efficiently (using the composite index) and multi-column queries with filters (using the same composite index structure), thereby maintaining both optimization speed and query flexibility.
Data Source
AI summary
A document is received, the document including metadata for a data visualization of a data set. The data set includes a plurality of data columns, each of the plurality of columns having a column name and a plurality of data values. A first set of columns of the plurality of columns is present in the data visualization. The first set of columns is determined based on the metadata. A second set of columns of the plurality of columns is determined, where the second set of columns includes remaining columns of the plurality of columns excluding the first set of columns. The data set is ordered by having the first set of columns prior to the second set of columns. A composite index is generated on the ordered data set.