5 ways to optimize your eloquent queries

Did you know that 1 in 4 visitors would abandon a website if it takes more than 4 seconds to load? While multiple factors can slow down a website, one common factor is inefficient database queries.

Eloquent is a great tool that makes it easy to interact with the database in our Laravel applications, but sometimes we forget that what looks like a method call or property is running a database query under the hood, which might lead to slow page loads or high memory usage. Here I some techniques that can improve your load times in your Laravel application.

1. Select only the columns you need

One way we can optimize a query is by reducing the amount of data we fetch from the database. When you make a query, the data that the database returns is sent over the network. The more data there is the more time it will take, but that's not all, all that data has to be stored in memory for the lifetime of the request and that can cause your server to slow down and run out of memory under heavy loads.

Fortunately, in Laravel, we can specify exactly the data we need. Let's say for example that we have an e-commerce website, and we want to display a list of products. Our controller would look something like this:

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

While doing this might seem reasonable (we are even paginating the results!), there may be a lot of information in our products' table that we don't need for a listing page (body, category id, product type, etc). You can reduce the amount of data by selecting only the columns you need:

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

This would make our query more efficient, this is especially important since the products' table would presumably contain a body column of type TEXT which could be quite large.

This technique is most effective in pages where you need to work with a ton of records, so you probably won't see a big change in memory usage/performance if you apply it to a page that fetches a few database records, nevertheless, it's still good to keep it in mind.

2. Beware of N+1 issues

Continuing with our e-commerce example, let's say that we need to display the brand that each product belongs to. Let's imagine we have a brand relationship in our Product model:

public function brand()
{
    return $this->belongsTo(Brand::class);
}

If we need to access the brand name in our blade template, it would look something like this:

@foreach ($products as $product)
    //...
    <p>{{ $product->brand->name }}</p>
    //...
@endforeach

Again, this looks fine. I have written code like these counties times in the past. However, there is a small issue here that might not be obvious. While we loaded all of our products into memory using one SQL query in our controller, the brands are retrieved from the database one by one in our forloop. If your relationship isn't loaded, Laravel will execute an SQL query to retrieve it from the database.

That issue is called "n+1", it's called that because we execute 1 SQL query to fetch our product and then N SQL queries to fetch the brands, N being the number of products.

Laravel offers a simple way to fix this issue using eager loading. Eager loading means that we want to retrieve all the related models before using them. Under the hood, Laravel will make only one SQL query to retrieve every related brand. So instead of making N+1 queries, we only make 2.

To use eager loading, you call the with method in your query

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

We can even combine the previous technique and this one by selecting only the columns we need from our relationship, we can do this by adding : followed by the columns we want to select from the relationship.

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

This way, Laravel will make sure to only select the id and name columns when eager loading the relationship.

3. Don't fetch all the records if you only need one

Let's say that in our store, we want to display all the users and show the total of the last order they made, we could do this in our blade template

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

Here we are simply iterating over all our users, then making a query to fetch the orders sorted by created_at (the latest() method takes care of that) and then we execute the query fetching only the first result, meaning the last order created, after that we simply access the total on that model.

You might have realized, however, that this is introducing an N+1 one issue. But we know how to fix that using eager loading!. Well, eager loading might not do what we want, if we are going to fetch all the orders into memory instead of only the most recent one.

There are a couple of ways to fix this in Laravel. One of them is by defining a hasOne relationship in our User model that retrieves only the last order created:

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

By using the method latestOfMany we tell Laravel that we only need the most recently created record, you can also use the oldestOfMany method if you want the oldest record instead.

Now that we have a new relationship, we can eager load it like any other relationship.

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

4. Use indexes

Database indexes can get pretty complicated if you want to know how they work, however you don't need to know too much about them to make use of them. In the simplest terms, indexes are sort of a look-up table that your database can consult when searching for a record, and using them can make your lookup queries a lot faster.

You can use indexes in Laravel by adding them to your migrations. Let's say that we expect our store users to search the products by name a lot, we could add an index query to the title of the products:

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

By creating this migration, we can speed up our table. Keep in mind, however, this index will only be applied if we search the product using the whole title or the start of the title. In other words, the index will be applied if we run queries like this:

Product::where('title', '=', $search);
Product::where('title', 'like', $search . '%');

But it won't be applied if we try to match by using a like query like this:

Product::where('title', 'like', '%' . $search . '%');

This can be solved using a full-text index, there are other types of indexes which are outside the scope of this article.

5. Optimizing circular relationships

Let's say that our product model looks like this,

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,
        ]);
    }
}

In this model, we have a helper function that uses the related category slug to generate the URL, a simple but useful method in our model. However, let's assume that we want to show all the products for a given category, our controller would look something like this:

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

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

Now let's say that we want to show the URL of the product on our view by calling the url method that we defined in the product model:

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

The problem here is that we've introduced an N+1 issue once again, when we call the url method in the product, we are making a query to fetch the category of each product even though we already have it!. One way to solve this is by eager loading the category in our products. We can do this by adding a .category to the load method call:

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

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

Now the N+1 issue is solved, but we are making two SQL queries to fetch the same category, once when the model gets injected into the show method in the controller and the other when we call the load method on that category. Fortunately, there is a way to avoid that by assigning the relationship directly using the setRelation method.

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

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

If you want to learn more about this technique, you can read this amazing article by Jonathan Reinink.

Conclusion

And that's it, I hope that you learned at least one new thing from this article and you found it valuable, and you can use them to make your website faster.

If you found this article valuable, make sure to follow me on Twitter, I post tips and other Laravel content there, also you can ask me any other questions you have about this or other Laravel topics.