News

The Index Experiment

We started with the obvious solution—adding more database indexes. If MySQL was struggling with complex searches, more indexes should fix it, right? We added composite indexes, covering indexes, and partial indexes across our wide tables.

The results were mixed. Some queries got faster, but others became painfully slow. Worse, saving data started taking longer because MySQL had to update all those indexes every time. We had created a performance seesaw.

To optimize our database structure, we also consolidated many of the searchable columns into a single JSON column, reducing the table width and improving MySQL’s performance for non-search operations. However, this made searching even more complex, as JSON queries in MySQL are notoriously slow and limited.

Why We Couldn’t Just Switch

The logical next step seemed obvious: migrate everything to a dedicated search engine like OpenSearch. But here’s the catch—our application wasn’t just a simple database with some queries. We had built complex syncing logic that automatically pulled data from various external services and kept everything in sync using MySQL’s reliable structure.

Switching to OpenSearch meant rewriting all of that—not just tweaking a few queries, but rebuilding the entire foundation of how our app handles data. We were looking at months of risky development work.

There had to be a better way.

The Hybrid Solution: Best of Both Worlds

Instead of choosing between MySQL and OpenSearch, we decided to use both. The idea was simple: let OpenSearch handle the complex searching and filtering, while MySQL continues managing data integrity, relationships, and business logic.

Here’s how our hybrid architecture works:

The Architecture

OpenSearch serves as our search layer, storing searchable copies of our data optimized for complex queries across multiple fields. It excels at fuzzy matching, sorting by any column combination, and handling the 47+ searchable fields our client requested.

MySQL remains our source of truth for all data creation, updates, and business logic. Our third-party integrations, sync processes, and complex relationships stay exactly where they are—no rewriting required.

The magic happens in the integration layer. When you query our system, OpenSearch finds the matching records and returns their IDs. We then use those IDs to fetch the full data from MySQL, maintaining all relationships and computed fields.

The Implementation

We built a custom query builder that mirrors Laravel’s Eloquent syntax but works with OpenSearch under the hood. Here’s how it looks in practice:

$records = config('services.opensearch.enabled', true) ? Lead::queryOSS() : Lead::query();
$user = $request->user();

$sorting = $this->getSortingParams($request,
    'leads/{category}',
    'leads-main-listing'
);
$records = $records->filterBy(collect($request->all()), $category, user());
$records = $records->sortBy($sorting, $user);
$records = $records->paginate($sorting['ipp']);

The beauty is in the simplicity—changing from Lead::query() to Lead::queryOSS() was literally the only change needed in most of our controllers.

OpenSearch Configuration

Setting up OpenSearch required careful configuration to handle our specific data structure. Here’s our configuration setup:

'<index_name>' => [
    'name' => env('OPENSEARCH_INDEX_RECORDS', '<index_name>'),
    'main_index' => env('OPENSEARCH_INDEX_RECORDS', '<index_name>').'_main',
    'migration_index' => env('OPENSEARCH_INDEX_RECORDS', '<index_name>').'_migration',
    'settings' => [
        'number_of_shards' => 1,
        'number_of_replicas' => 1,
        'max_result_window' => 50000,
    ],
    'mappings' => [
        'date_detection' => true,
        'dynamic_date_formats' => [
            'MM/dd/yyyy||yyyy-MM-dd||strict_date_optional_time',
        ],
        'dynamic' => true,
        'dynamic_templates' => [
            [
                'strings_as_keywords' => [
                    'match_mapping_type' => 'string',
                    'mapping' => [
                        'type' => 'keyword',
                    ],
                ],
            ],
        ],
    ],
],

Key configuration decisions:

We enabled dynamic mapping to automatically handle new fields without manual schema updates, perfect for our evolving data structure. Most of our searchable fields are exact matches rather than full-text searches, so we configured strings to be indexed as keywords by default.

We configured multiple date formats to handle the various date formats coming from our JSON columns and third-party integrations. Additionally, we set a high max_result_window to support large exports and reports that our users frequently generate.

Edge Cases/Trade-offs

The most challenging part is the data synchronization from MySQL to OpenSearch. To make it work reliably, you must be 100% sure that you’ve mapped the correct field types between the two systems.

In MySQL, we were saving booleans as 1 and 0 (one bit), but OpenSearch has a dedicated boolean type. You could configure a custom parser to accept 0 and 1, but it’s better to handle this conversion in your sync code and keep the input to OpenSearch as proper true/false values.

We sync all strings as keyword fields since we don’t need fancy full-text operations. Wildcard searches work fine on keywords, and all MySQL-style searching continues to work. We don’t need to tokenize our strings, which simplifies the mapping.

Different date formats between MySQL and OpenSearch can cause sync failures. We handle multiple date formats in our configuration, but it’s crucial to standardize date handling in your sync logic.

Unlike a single database, you now have two sources of truth that can potentially drift apart. Your sync logic must be bulletproof, with proper error handling and retry mechanisms.

Data Synchronization

We implemented automatic syncing using Laravel’s model observers. Whenever a record is created, updated, or deleted in MySQL, an event triggers a background job that updates the corresponding document in OpenSearch:

public function created(Lead|Model $model): void
{
    $this->dispatchOSSSync($model);
}

public function updated(Model $model): void
{
    foreach ($model->getOSSFields() as $field) {
        if ($model->isDirty($field)) {
            $this->dispatchOSSSync($model);
            break;
        }
    }
}

OSS Interface and Trait Implementation

The getOSSFields() method is defined in an interface that all OpenSearch-searchable models must implement. This ensures consistent behavior across different models and makes it easy to define which fields should be synced to OpenSearch.

We also created an OssSearchable trait that implements the required configurational methods:

trait OssSearchable
{
    public static function queryOSS(): OSSQuery
    {
        $builderClass = self::getQueryBuilderClass();
        
        return new $builderClass(
            self::getOssIndexName(),
            targetModelClass: self::class
        );
    }

    protected static function getOssIndexName(): string
    {
        $table = (new self)->getTable();
        return config("opensearch.indexes.$table.name");
    }

    protected static function getQueryBuilderClass(): string
    {
        return OSSQuery::class;
    }
}

This trait handles the configuration boilerplate, making it simple to add OpenSearch capabilities to any model by just implementing the interface and using the trait.

Zero-Downtime Index Management

One of the biggest challenges with search engines is updating index settings or mappings without downtime. We solved this with an index swapping strategy:

1. Create New Index: When we need to update index settings, we create a new index with the updated configuration
2. Migrate Data: We reindex all data from the old index to the new index using OpenSearch’s reindex API
3. Atomic Swap: We use index aliases to atomically switch traffic from the old index to the new one
4. Cleanup: Once the swap is complete, we delete the old index

This approach ensures that our application continues to work seamlessly even during major index updates, maintaining high availability while allowing us to evolve our search infrastructure.

The Query Builder Magic

Our OpenSearch query builder maintains familiar Eloquent methods while translating them to OpenSearch queries behind the scenes:

public function get(): array
{
    $results = $this->openSearchService->searchByQuery(
        query: $this->buildQuery(),
        index: $this->index,
        sort: $this->sort,
        searchAfter: $this->searchAfter,
        limit: $this->limit
    );

    if ($this->targetModelClass) {
        $records = $this->getMySQLRecords($results['records']);
    } else {
        $records = Collection::make($results['records'])->map(function ($record) {
            return OSSDocument::fromRawResponse($record);
        });
    }

    return [
        'records' => $records,
        'total' => $results['total'],
    ];
}

The getMySQLRecords method is where the magic happens—it takes the OpenSearch results (which contain document IDs) and fetches the full records from MySQL:

protected function getMySQLRecords(array $ossRecords): EloquentCollection
{
    $uuids = Collection::make($ossRecords)->map(function ($record) {
        return $record['_id'];
    });

    $records = $this->targetModelClass::whereIn('uuid', $uuids)->get();

    // Create a map of positions to maintain OpenSearch sort order
    $orderedUuids = array_flip($uuids->toArray());
    
    // Sort the collection based on the original OpenSearch order
    $records = $records->sortBy(function ($model) use ($orderedUuids) {
        return $orderedUuids[$model->uuid];
    })->values();

    return $records;
}

Developer Experience

The best part? Our existing Eloquent methods work seamlessly with the OpenSearch layer:

• ->count() - Returns total matching records
• ->get() - Fetches all matching records from MySQL
• ->paginate() - Handles pagination with OpenSearch sorting
• ->chunk() - Processes large datasets in batches
• ->first() - Gets the first matching record

All familiar Laravel patterns continue to work, but now with OpenSearch’s powerful search capabilities under the hood.

The Results

This hybrid approach delivered everything we needed:

Search queries that previously took 2-3 seconds now complete in under 200ms, even with complex filtering across multiple fields. We can now search and sort by any combination of our 47+ fields without creating dozens of database indexes.

Most importantly, we achieved this without massive rewrites. Our existing business logic, relationships, and third-party integrations remained untouched. OpenSearch handles the heavy lifting for search operations, while MySQL continues managing data integrity and relationships.

The hybrid architecture proved that sometimes the best solution isn’t choosing between technologies—it’s making them work together.

When Should You Use This Approach?

Use the Hybrid Approach When:

• Your dataset is small to medium-sized: MySQL can handle millions of records efficiently, but struggles with complex search operations across many fields. This hybrid approach works best when your data volume is manageable but you need extensive search flexibility.
• You’re extending an existing application: If your Laravel app has been working fine with MySQL, it means your data scale is appropriate. The hybrid approach is perfect for adding advanced search capabilities without the risk and cost of a complete rewrite.
• You have many searchable fields: When you need to search and sort across dozens of columns without creating performance-killing indexes, OpenSearch excels while MySQL handles the relationships and business logic.
• You value development speed: Changing from ->query() to ->queryOSS() in existing controllers is much faster than rebuilding your entire data layer.

Don’t Use This Approach When:

• You’re dealing with truly massive datasets: If you’re expecting millions of records with high growth rates, it’s better to invest in a complete architectural overhaul using dedicated NoSQL solutions like DynamoDB or a full OpenSearch implementation.
• You don’t actually need it: Don’t add complexity just because it looks cool. The hybrid approach introduces synchronization overhead, type conversion challenges, and additional infrastructure. Only implement it when your application genuinely needs advanced search capabilities.
• Your current setup works fine: If your MySQL queries are fast enough and your users are happy with the current search functionality, the added complexity isn’t worth it.
• You can’t handle the operational complexity: Managing two data stores means dealing with synchronization issues, data consistency concerns, and more complex deployment and monitoring requirements.

Remember, every architectural decision is a trade-off. The hybrid approach trades some complexity for search performance and flexibility—make sure that trade-off makes sense for your specific use case.