Optimize Eloquent Performance in Laravel

Larry Barker
16 Sep, 2024

Introduction

In a recent challenge, a significant number of developers submitted their pull requests to address Eloquent Performance Optimization. This post outlines a practical approach to improving an inefficient controller scenario involving 1000 queries, 11,000 models, and excessive RAM usage. Additionally, we’ll touch on how to preview Laravel pull request changes effectively.

Initial Scenario

On a local machine, the initial code resulted in:

1101 queries
11,000 models
20 megabytes of RAM
A runtime of three seconds

The main focus is on the controller query and the home blade. However, a critical oversight was the database migrations.

Database Migrations

Adding an Index

A key issue identified was the absence of a foreign key and an index on the movie ID in the movie ratings table. Without an index, the query performance suffers significantly. To address this, one can use PHP artisan to create a migration that adds a foreign key to the ratings table:

$table->foreign('movie_id')->references('id')->on('movies');

This foreign key not only maintains referential integrity but also creates an index automatically. After adding the foreign key and rerunning the migration, there’s a noticeable improvement in query performance, reducing the duration by about 60%.

Initial Database Setup

An alternative approach involves recreating the migration if no data exists in the database. From the beginning, instead of using unsigned big integer, use:

$table->foreignId('movie_id')->constrained();

Then, rerun migrate:fresh to apply these changes to a fresh database.

Controller and View Optimization

Avoiding N+1 Queries

In the home blade, fetching the movie category name for every movie triggers an N+1 query issue. To mitigate this, use:

Movie::with('category')->get();

This reduces the number of queries by fetching related categories in a single query.

Efficient Data Fetching

Fetching all movie data with categories and then sorting is highly inefficient. Instead:

Use database ordering, grouping, and sorting before fetching the data.
Limit the results to 100 movies to avoid memory overload.

Leveraging Laravel Features

Laravel introduced withAverage in version 8.30, which can be utilized along with withCount:

Movie::with('category')
    ->withCount('ratings')
    ->withAvg('ratings', 'rating')
    ->orderBy('ratings_avg_rating', 'desc')
    ->limit(100)
    ->get();

This approach orders movies by their average rating and limits the results, significantly reducing the number of queries and RAM usage.

Adjusting Relationships

To fetch the count of ratings efficiently, adjust the relationship to avoid fetching all ratings:

Movie::withCount('ratings')->get();

This ensures only the count is fetched, reducing the number of queries and memory usage.

Conclusion

This post outlines an effective solution for optimizing Eloquent performance by addressing database schema issues, avoiding N+1 queries, and leveraging Laravel’s advanced querying features. Various techniques, including query builder and raw queries, were explored by other developers, which will be discussed separately in future posts. Moreover, learning how to preview Laravel pull request changes is essential for evaluating the optimization efforts efficiently.

For an effortless way to preview your Laravel pull request changes and ensure your optimizations are on point, try Glimpse. With Glimpse, you can automatically deploy your GitHub pull requests to preview environments with the help of Laravel Forge. Start optimizing smarter today!