Maximizing Laravel Performance: Proven Query Optimization Techniques

In the fast-paced world of web development, performance is frequently the deciding factor. Today's visitors expect lightning-fast websites, and any delays might lead to dissatisfaction. Inefficient database queries are a big contributor to slow-loading web pages. The popular PHP framework Laravel has a sophisticated feature called "Eloquent" for streamlining database operations. However, complicated database queries may be run beneath the surface of relatively simple method calls, thereby causing sluggish page loading and excessive memory consumption. In this post, we'll look at eight key approaches for optimizing Laravel queries and ensuring your apps run at their best.

1. Eager Loading: Supercharge Your Relationships

Laravel's secret weapon for optimizing database queries is eager loading. It's all about fetching related data ahead of time rather than loading it as needed. This can help you avoid the dreaded "N+1" query problem. Using the with method instructs Laravel to retrieve related models in a single query, greatly improving efficiency. When working with complicated interactions, such as fetching posts and their comments in a forum application, eager loading is required.

public function index()
{
    return view('products', [
        'products' => Products::query()
            ->select(['id', 'title', 'slug', 'thumbnail'])
            ->with('comments') // Eager loading the comments
            ->paginate()
    ]);
}

2. Select Only the Columns You Need: A Leaner Approach

To begin optimizing your searches, pick only the columns you require. The size of the dataset returned by a query has an impact on the performance of your application. Laravel allows you to specifically define the columns to fetch, which reduces the amount of data carried over the network and the amount of RAM used. This method is especially useful when working with big record sets. For example, if you're creating a product listing page for an e-commerce website, choose only the most important product details to improve query efficiency.

Let us check the following code which returns a products collection:

public function index()
{
    return view('products', [
        'products' => Products::query()->paginate()
    ]);
}

And in the blade file we use the data to display the products as follows:

@foreach($products as $product)
    <a href={{ route('products.show', $product->slug) }}>
        <img src="{{ $product->thumbnail }}" alt={{ $product->title }}>
        {{ $product->id }} ) {{ $product->title }}
    </a>
@endforeach

We can see we are using only "id", "title", "slug" and "thumbnail" columns. Luckily Laravel provide a way to select the columns we need instead of selecting and returning all the columns:

public function index()
{
    return view('products', [
        'products' => Products::query()
            ->select(['id', 'title', 'slug', 'thumbnail'])
            ->paginate()
    ]);
}

3. Use Indexes: A Quick Path to Data Retrieval

Indexes act as a navigation system for your database queries. They offer a quick way to find records, considerably improving query performance. You improve your database's ability to find and retrieve data by adding indexes to certain columns. For example, if your customers commonly search for things by name, adding an index to the product title can speed up these searches. Keep in mind that indexes operate best when you search using the entire title or the beginning of the title.

Schema::table('products', function (Blueprint $table) {
    $table->string('title')->index();
});

4. Don't Fetch All Records if You Only Need One: A Precise Query

Getting all records when you just require one can be inefficient, leading to the "N+1" dilemma. Laravel provides solutions to this problem via relationships. You can improve your queries by specifying a special relationship that retrieves only the most recent or oldest data. This is very beneficial for retrieving the most recent order for each user. Loading the latest order relationship as soon as possible ensures rapid retrieval of this data.

Let's imagine we want to showcase all users in our store and show the total of the latest order they made, we could implement this in our blade template.

@foreach($users as $user)
    <p>{{ $user->name }}</p>
    <p>{{ $user->orders()->latest()->first()->total }}</p>
@endforeach

We are simply iterating over all of our users, then creating a query to retrieve orders sorted by "created_at" (the "latest()" function handles this), and then we execute the query, retrieving only the first result, which is the most recent order created, and then we simply access the total on that model.

However, you may have seen that this introduces a "N+1" one problem. But we know how to fix that using eager loading!. If we wish to get all orders into memory instead of just the most recent one, eager loading might not work.

In Laravel, there are a few options for dealing with issue. One of them is to define a hasOne relationship in our User model that returns only the most recently created order:

function lastOrder()
{
    return $this->hasOne(Order::class)->latestOfMany();
}

We tell Laravel that we only require the most recently created record by using the "latestOfMany" method; you can alternatively use the "oldestOfMany" method if you want the oldest record.

Now that we have a new relationship, we may treat it as if it were any other.

public function index()
{
    return view('users', [
        'users' => Users::query()
            ->with('lastOrder')
            ->get();
    ]);
}

And in our template now, we can fetch the total by accessing our loaded relationship.

@foreach($users as $user)
    <p>{{ $user->name }}</p>
    <p>{{ $user->lastOrder->total }}</p>
@endforeach

5. Optimizing Circular Relationships: Breaking the Cycle

Circular relationships in your Laravel models can cause repetitive queries and inefficiencies. While eager loading might be beneficial, it is crucial to avoid "double-dipping" in the database. Duplicate queries are removed by simply setting the relationship with the setRelation function. This strategy is crucial when working with models that include circular or interwoven interactions. Reduce queries and enhance performance by optimizing your code.

Let's consider a scenario with our Product model structured as follows:

class Product extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function url()
    {
        return URL::route('product', [
            'category' => $this->category->slug,
            'product' => $this->slug,
        ]);
    }
}

This model has a useful function that generates a URL using the relevant category's slug, a simple but useful way in our model. However, suppose we want to show all of the products in a given category. In this example, our controller might look like this:

public function show(Category $category)
{
    $category->load('products'); // eagerly load the products

    return view('categories.show', ['category' => $category]);
}

If we want to display the URL of each product in our view by invoking the 'url' method defined in the Product model, we might use a loop like this:

@foreach($category->products as $product)
<li>
    <a href="{{ $product->url() }}">{{ $product->name }}</a>
</li>
@endforeach

The reappearance of the "N+1" problem is the issue here. When we call the "link" method on each product, it launches a query to retrieve the category for each product, despite the fact that we already have that information. One solution would be to eagerly load the category within our products. We may accomplish this by adding ".category" to the "load" method:

public function show(Category $category)
{
    $category->load('products.category'); // eagerly load the products

    return view('categories.show', ['category' => $category]);
}

While this overcomes the "N+1" problem, it necessitates two SQL requests to retrieve the same category—once when the model is injected into the controller's "show" function, and again when we run the "load" method on that category. Fortunately, there is a more efficient approach to address this by assigning the connection directly with the'setRelation' method:

public function show(Category $category)
{
    $category->products->each->setRelation('category', $category);

    return view('categories.show', ['category' => $category]);
}

This method simplifies the process by removing unnecessary queries and maintaining optimal database performance.

6. Avoid Subqueries and UNIONs: JOIN for Speed

"Subqueries" and "UNIONs" can dramatically slow down your queries. Instead, whenever possible, use "JOINs". Because they aggregate data from numerous tables into a single result set, JOINS are faster and more efficient. For example, you may utilize Laravel's Query Builder to conduct fast JOIN operations, lowering query execution time and improving application performance.

$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

7. Cache: Boosting Speed and Efficiency

Caching is a great method for improving the performance of repetitive queries. Laravel includes simple caching methods for storing query results for a set length of time. Caching not only accelerates query execution, but it also minimizes the burden on your database server. You may substantially increase the efficiency of your Laravel application by strategically integrating caching, especially when dealing with frequently visited data.

$value = Cache::remember('users', 60, function () {
    return DB::table('users')->get();
});

Conclusion

Optimizing database queries in your Laravel application is an important step toward achieving peak performance. These query optimization approaches can make a substantial difference whether you're constructing a modest personal project or a high-traffic e-commerce website. A few options to consider are eager loading, selective column retrieval, index utilization, and efficient query design. Adopt these recommended practices, and your Laravel application will run like a well-oiled machine. Your customers will appreciate faster service.

Many times we need to upload files in out app and store them in storage along with the path in database. I've created a article Mastering File Uploads in Laravel 10 Validation and Database Storage where I discussed about uploading files in in details and simple way.

Happy Coding !