Custom Indexing for Wildcard Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in executing complex queries, particularly those with wildcard criteria, due to the need for substantial computational resources and inefficient access methods in multi-tenant and dynamic-schema architectures.
Innovation Solution
Implementing custom indexing and a two-stage filtering mechanism, where a less restrictive filter is used to access a custom index, followed by a second stage to filter results, optimizing query execution by reducing disk accesses and improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional database query methods are used for complex queries with wildcard criteria, then query execution can handle general cases, but execution time increases substantially and computational resources are excessively consumed
Solution Approach 1:
The patent creates custom indexes beforehand that store pre-computed values for wildcard patterns. When a query with wildcard criteria is executed, the system can directly access these pre-prepared indexes instead of computing results in real-time, significantly reducing execution time and resource consumption.
Solution Approach 2:
The patent creates a copy of the base table data in a custom index structure that is optimized for wildcard queries. This custom index contains the same data but organized in a format that enables efficient pattern matching, allowing the system to query this copy rather than scanning the entire base table.
2Productivity
If standard indexing methods are used in multi-tenant and dynamic-schema architectures, then database storage is simplified, but query access efficiency deteriorates due to architectural complexities
Solution Approach 1:
The patent segments the indexing task by creating separate custom indexes for each tenant and schema configuration. Instead of maintaining a single complex index structure that must accommodate all tenants and schemas, the system creates specialized indexes tailored to each tenant's specific requirements, simplifying the indexing process while improving query efficiency.
Solution Approach 2:
The patent applies local quality by customizing index structures according to each tenant's specific needs and query patterns. Each tenant can have indexes optimized for their particular data characteristics and access requirements, rather than using a one-size-fits-all indexing approach that must accommodate all possible scenarios.
3Measurement precision
If comprehensive filtering is applied to ensure accurate query results, then result precision is maintained, but the number of disk accesses increases substantially
Solution Approach 1:
The patent performs filtering operations in advance during index creation and maintenance. The custom indexes store pre-filtered and organized data that is optimized for specific query patterns, allowing the system to retrieve results with high accuracy without performing extensive filtering operations during query execution that would require additional disk accesses.
Data Source
AI summary
A database query may be determined based on a database query definition. The database query definition may include a filter criterion that contains a wildcard match, which may include a first fixed portion and a second wildcard portion. The first fixed portion may include one or more combining characters. The database query may include a first query portion including a first canonical representation of the first fixed portion that omits the one or more characters. The database query may include a second query portion including a second canonical representation of the first fixed portion. The database query may be executed to select a result set that includes a plurality of query result values by applying the second query portion to filter values accessed by the first query portion.