Search Engine Indexing for Relational Database Join Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database design techniques are inadequate for efficiently querying and retrieving structured data stored in multiple tables, as they do not support full-text indexing and querying, leading to inefficiencies such as increased index size and resource-intensive data retrieval when using search engines in enterprise environments.
Innovation Solution
A method and system that utilize a search engine to index multiple database tables, map rows to documents, and enforce join constraints using join mappings, allowing for efficient execution of join queries without retrieving large volumes of data from disk, and ranking documents based on combined scores.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional database design techniques are used to store structured data in multiple tables, then data redundancy is reduced and data normalization is improved, but full-text indexing and querying capability deteriorates
Solution Approach 1:
The patent segments the database tables into multiple indexable units by creating separate index structures for each table. Each table is indexed independently with its own index structure, allowing full-text indexing capabilities to be applied to each table separately while maintaining the normalized database schema. This segmentation enables the system to handle both normalized data storage and full-text querying requirements simultaneously.
Solution Approach 2:
The patent introduces an intermediary indexing mechanism that bridges the normalized database structure and full-text querying requirements. The indexing system acts as an intermediary layer between the normalized tables and the query processor, enabling full-text search capabilities without compromising data normalization. The intermediary index structures allow efficient text searching while the normalized tables maintain their referential integrity and data redundancy benefits.
2Ease of operation
If search engines are used to query data from multiple tables, then ease of operation is improved, but device complexity increases
Solution Approach 1:
The patent implements a universal search engine interface that can query multiple database tables through a single unified interface. The search engine is designed to handle queries across different tables and data types using the same operational mechanisms, eliminating the need for separate query interfaces for each table. This multi-functionality maintains ease of operation while managing system complexity through standardized query processing.
Solution Approach 2:
The patent creates simplified copies of database table structures as indexable representations. Instead of working directly with the complex normalized database schema during queries, the system uses simplified index structures that replicate the essential data relationships in a query-friendly format. These copies enable simple search operations while the original normalized tables maintain their structural complexity for data storage efficiency.
3Loss of information
If data is retrieved from disk to process join queries, then completeness of results is improved, but loss of time and energy increases
Solution Approach 1:
The patent performs preliminary indexing of database tables before query execution. Index structures are pre-built and stored in memory or near-memory storage, containing pre-processed data relationships and search keys. When join queries are executed, the system retrieves only the necessary index portions from this pre-processed state rather than reading entire tables from disk, significantly reducing I/O operations and energy consumption while maintaining complete query results.
Solution Approach 2:
The patent implements dynamic data retrieval strategies where the system adjusts the amount of data retrieved from disk based on query requirements and data access patterns. The indexing system dynamically loads only the necessary data portions into memory during query processing, rather than statically loading entire tables. This dynamic approach ensures complete results when needed while minimizing energy consumption by reducing overall data retrieval volume.
Data Source
AI summary
Techniques and systems for indexing and retrieving data and documents stored in a record-based database management system (RDBMS) utilize a search engine interface. Search-engine indices are created from tables in the RDBMS and data from the tables is used to create “documents” for each record. Queries that require data from multiple tables may be parsed into a primary query and a set of one or more secondary queries. Join mappings and documents are created for the necessary tables. Documents matching the query string are retrieved using the search-engine indices and join mappings.