Efficient Multi-Keyword String Search in SQL: Query Strategies and Optimization

Fundamental Approaches to Multi-Keyword String Search

Searching for strings that contain multiple specific keywords is a common requirement in relational database applications. The most straightforward approach utilizes SQL's LIKE operator with wildcard patterns. For the scenario requiring column C to contain all three keywords "David", "Moses", and "Robi", the basic query structure is:

SELECT * FROM T WHERE 
  C LIKE '%David%' AND 
  C LIKE '%Moses%' AND 
  C LIKE '%Robi%'

This method employs clear logic: multiple LIKE conditions connected by AND operators ensure the target column satisfies all keyword matching requirements. The percent (%) wildcard represents any sequence of characters, enabling matches regardless of keyword position within the string.

Performance Analysis and Optimization Considerations

While functionally adequate, this approach presents significant performance limitations. LIKE queries with leading wildcards (e.g., '%David%') typically cannot leverage standard B-tree indexes effectively, as the database cannot determine match starting positions. This often results in full table scans, causing substantial performance degradation with large datasets.

Empirical testing reveals that with a table T containing 1 million rows, three-keyword LIKE queries may require several seconds to execute. The primary performance bottlenecks include:

1. Each LIKE condition necessitates string matching across the entire column
2. AND logic requires multiple scans of the same dataset
3. Leading wildcards restrict index optimization possibilities

Full-Text Indexing Solutions

To address LIKE query limitations, modern database systems offer full-text indexing solutions. In SQL Server, for instance, enabling full-text indexing allows using the CONTAINS function for more efficient searching:

SELECT * FROM T 
WHERE CONTAINS(C, '"David" AND "Moses" AND "Robi"')

Full-text indexing creates specialized inverted index structures that decompose text content into tokens and establish rapid lookup mechanisms. Compared to LIKE queries, CONTAINS queries offer several advantages:

• Support for Boolean operators (AND, OR, NOT)
• Stemming analysis and synonym expansion capabilities
• Prefix search and proximity query support
• Typically an order of magnitude faster than LIKE queries

Prefix search example:

SELECT * FROM T 
WHERE CONTAINS(C, '"David*" OR "Robi*" OR "Moses*"')

Implementation Details and Best Practices

Selecting an appropriate query strategy requires consideration of multiple factors:

1. Data Volume: LIKE queries suffice for small datasets (<100k rows); full-text indexing recommended for large datasets
2. Query Frequency: High-frequency queries justify full-text index maintenance costs
3. Functional Requirements: Need for fuzzy matching, stemming, or advanced features
4. Database Platform: Variations in full-text indexing implementations across systems

Character escaping example:

-- Handling searches with special characters
SELECT * FROM T WHERE C LIKE '%<br>%'  -- Searching for HTML tag text
SELECT * FROM T WHERE C LIKE '%&amp;%'   -- Searching for escaped & symbols

Comparative Analysis and Selection Guidelines

Based on comparative analysis, the following practical recommendations emerge:

For most production environments, particularly those handling substantial text data, full-text indexing represents the recommended approach. While requiring additional configuration and maintenance, the performance improvements and functional enhancements typically justify the investment.