When building search functionality for data-heavy applications, Meilisearch combined with Laravel Scout provides an excellent solution for fast and relevant search results. However, when dealing with related data across multiple tables, you might encounter significant performance issues during the indexing process. In this post, I’ll share a practical solution we discovered that dramatically improved our indexing speed using SQL views.

The Problem: Slow Indexing with Related Data

Let’s illustrate this with a practical example. Imagine we’re building a bookstore application with the following structure:

class Book extends Model
{
    use Searchable;

    public function author()
    {
        return $this->belongsTo(Author::class);
    }

    public function publisher()
    {
        return $this->belongsTo(Publisher::class);
    }

    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }

    public function toSearchableArray()
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'author_name' => $this->author->name,
            'publisher_name' => $this->publisher->name,
            'publisher_city' => $this->publisher->address->city,
            'categories' => $this->categories->pluck('name')->toArray(),
            'isbn' => $this->isbn,
            'price' => $this->price,
            'published_at' => $this->published_at,
        ];
    }
}

When indexing this data with Laravel Scout, each book record needs to:

Load the author relationship
Load the publisher relationship
Load the publisher’s address
Load all categories

For a few hundred books, this might be acceptable. But when dealing with thousands or millions of records, the performance hit becomes significant. In our case, indexing 10,000 records took several hours due to the N+1 query problem and relationship loading overhead.

The Solution: SQL Views

Instead of letting Laravel Scout load all these relationships at runtime, we can create a SQL view that pre-joins all the necessary data. Here’s how:

First, create a migration for the view:

public function up()
{
    DB::statement("
        CREATE VIEW searchable_books AS
        SELECT 
            books.id,
            books.title,
            books.isbn,
            books.price,
            books.published_at,
            authors.name as author_name,
            publishers.name as publisher_name,
            addresses.city as publisher_city,
            GROUP_CONCAT(categories.name) as category_names
        FROM books
        LEFT JOIN authors ON books.author_id = authors.id
        LEFT JOIN publishers ON books.publisher_id = publishers.id
        LEFT JOIN addresses ON publishers.address_id = addresses.id
        LEFT JOIN book_category ON books.id = book_category.book_id
        LEFT JOIN categories ON book_category.category_id = categories.id
        GROUP BY books.id
    ");
}

public function down()
{
    DB::statement("DROP VIEW IF EXISTS searchable_books");
}

Create a model for the view:

class SearchableBook extends Model
{
    use Searchable;

    protected $table = 'searchable_books';

    public function toSearchableArray()
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'author_name' => $this->author_name,
            'publisher_name' => $this->publisher_name,
            'publisher_city' => $this->publisher_city,
            'categories' => explode(',', $this->category_names),
            'isbn' => $this->isbn,
            'price' => $this->price,
            'published_at' => $this->published_at,
        ];
    }
}

The Results

After implementing this solution, our indexing speed improved dramatically:

Before: ~500 records per hour
After: ~10,000 records per hour (20x improvement)

The benefits of this approach include:

Reduced Database Queries: Instead of multiple queries per record, we now have a single, efficient query
Lower Memory Usage: No need to load and hydrate multiple Eloquent models
Faster Indexing: The view handles all the relationship joins at the database level
Maintainable Code: The view’s structure clearly shows what data is being indexed

Important Considerations

While this solution significantly improved our performance, there are a few things to keep in mind:

View Updates: The view’s data is only as fresh as your last database update. If you frequently update related data, you might need to reindex more often.

Memory Usage: For very large datasets, you might want to chunk the indexing:

SearchableBook::chunk(1000, function ($books) {
    $books->searchable();
});

Database Load: While the view itself is efficient, creating it might temporarily lock your tables. Plan the migration during off-peak hours.

Conclusion

SQL views provide an elegant solution to the performance challenges of indexing related data with Laravel Scout and Meilisearch. By moving the relationship resolution to the database level, we can achieve significantly faster indexing times while maintaining clean, maintainable code.

Remember, this solution is particularly useful when:

You have complex relationships that need to be indexed
Your dataset is large (thousands or millions of records)
Indexing performance is a bottleneck in your application