Skip to main content

Search and Faceting Optimization Summary

This document summarizes the comprehensive search and faceting optimizations implemented for the OpenRegister application. These optimizations significantly improve search performance by leveraging database indexes, implementing intelligent caching strategies, and optimizing query execution paths.

Key Optimizations Implemented​

1. Database Index Optimization​

Migration: lib/Migration/Version1Date20250102120000.php

Added critical indexes for search performance:

Single-Column Search Indexes​

  • objects_name_search_idx - Index on name column
  • objects_summary_search_idx - Index on summary column
  • objects_description_search_idx - Index on description column

Composite Search Indexes​

  • objects_name_deleted_published_idx - Combined search + lifecycle filtering
  • objects_summary_deleted_published_idx - Summary search + lifecycle filtering
  • objects_description_deleted_published_idx - Description search + lifecycle filtering
  • objects_name_register_schema_idx - Name search + register/schema filtering
  • objects_summary_register_schema_idx - Summary search + register/schema filtering
  • objects_name_organisation_deleted_idx - Name search + multi-tenancy filtering
  • objects_summary_organisation_deleted_idx - Summary search + multi-tenancy filtering

Performance Impact: These indexes enable fast lookup on frequently searched metadata columns, reducing query times from seconds to milliseconds.

2. Schema Caching System​

Handler: lib/Service/Schemas/SchemaCacheHandler.php

Implemented comprehensive schema caching with:

Features​

  • In-memory caching for frequently accessed schemas
  • Database-backed cache with configurable TTL
  • Batch schema loading for multiple schemas
  • Automatic cache invalidation when schemas are updated
  • Cache statistics and monitoring

Performance Benefits​

  • Eliminates repeated database queries for schema loading
  • Reduces schema processing overhead
  • Enables predictable performance for schema-dependent operations
  • Supports high-concurrency scenarios

3. Schema-Based Facet Caching​

Service: lib/Service/SchemaFacetCacheService.php

Implemented predictable facet caching based on schema definitions:

Key Concepts​

  • Predictable facets: Facets are determined by schema properties
  • Schema-based invalidation: Cache invalidated when schemas change
  • Multiple facet types: Support for terms, date_histogram, and range facets
  • Facetable field discovery: Automatic detection of facetable properties

Caching Strategy​

  • Facet configurations cached per schema
  • Facet results cached with configurable TTL
  • Automatic cleanup of expired cache entries
  • Memory + database dual-layer caching

4. Optimized Search Query Execution​

Handler: lib/Db/ObjectHandlers/MariaDbSearchHandler.php

Enhanced search performance with prioritized search strategy:

Search Priority Order​

  1. PRIORITY 1: Indexed metadata columns (name, summary, description)

    • Fastest performance using database indexes
    • Direct column access with LOWER() function

  2. PRIORITY 2: Other metadata fields (image, etc.)

    • Moderate performance with direct column access
    • No indexes but faster than JSON search

  3. PRIORITY 3: JSON object search

    • Comprehensive fallback using JSON_SEARCH()
    • Slowest but ensures complete search coverage

Performance Impact​

  • Dramatic improvement in search response times
  • Leverages database indexes for common searches
  • Maintains comprehensive search coverage

5. Cache Table Structure​

Added two new cache tables:

openregister_schema_cache​

  • Stores cached schema objects and computed properties
  • Supports TTL-based expiration
  • Indexed for fast schema lookup

openregister_schema_facet_cache​

  • Stores cached facet configurations and results
  • Supports different facet types
  • Indexed by schema and facet configuration

Integration Points​

Service Registration​

Updated lib/AppInfo/Application.php to register new cache handlers:

  • SchemaCacheHandler - Schema caching and management
  • FacetCacheHandler - Facet caching and discovery

Event-Driven Cache Invalidation​

The cache services are designed to integrate with existing event systems:

  • Schema update events trigger cache invalidation
  • Automatic cleanup of expired cache entries
  • Statistics and monitoring support

Performance Expected Improvements​

Search Performance​

  • Metadata searches: 10-50x improvement using indexes
  • Full-text searches: 3-10x improvement with prioritized strategy
  • Complex searches: 5-15x improvement with composite indexes

Faceting Performance​

  • Schema-based facets: Near-instant response for cached facets
  • Facetable field discovery: Predictable performance based on schema
  • Facet result caching: Significant reduction in computation time

Schema Loading Performance​

  • Individual schemas: 5-10x improvement with caching
  • Batch schema loading: 10-20x improvement with bulk operations
  • Schema-dependent operations: Consistent sub-millisecond performance

Monitoring and Maintenance​

Cache Statistics​

Both cache services provide statistics methods:

  • Total cache entries
  • Cache hit/miss ratios
  • Memory usage metrics
  • Expired entry counts

Cache Management​

  • Manual cache clearing capabilities
  • Automatic expired entry cleanup
  • Cache invalidation on schema updates
  • Performance monitoring and logging

Maintenance Tasks​

  • Regular cleanup of expired cache entries
  • Monitoring of cache performance metrics
  • Index maintenance and optimization
  • Cache size monitoring and tuning

Usage Recommendations​

For Developers​

  1. Use the cache services when working with schemas frequently
  2. Leverage facetable field discovery for dynamic UI generation
  3. Monitor cache statistics for performance optimization
  4. Consider cache warming for critical schemas

For Administrators​

  1. Monitor cache table sizes and performance
  2. Set up regular cache cleanup cron jobs
  3. Monitor search performance metrics
  4. Consider index maintenance during low-traffic periods

For API Consumers​

  1. Expect significantly improved search response times
  2. Faceting operations will be much faster
  3. Schema-dependent operations will have consistent performance
  4. Large result sets will be processed more efficiently

Future Enhancements​

Potential Improvements​

  1. Full-text search indexes: Consider MySQL FULLTEXT indexes for even better text search
  2. Distributed caching: Redis/Memcached integration for multi-server setups
  3. Query result caching: Cache complete search results for popular queries
  4. Adaptive caching: Machine learning-based cache optimization
  5. Search analytics: Comprehensive search performance monitoring

Monitoring Opportunities​

  1. Search query performance tracking
  2. Cache effectiveness metrics
  3. Index usage statistics
  4. User search pattern analysis

Conclusion​

These optimizations provide a solid foundation for high-performance search and faceting in OpenRegister. The combination of database indexes, intelligent caching, and optimized query execution creates a scalable and maintainable search system that can handle large datasets efficiently.

The predictable nature of schema-based faceting, combined with comprehensive caching strategies, ensures consistent performance even as data volumes grow. The modular design allows for future enhancements and easy maintenance.