13 March 2025
In this article, I will summarise what I learned from Jonathan Reinink course on database optimization tailored for Laravel applications using Eloquent ORM.
The first step in optimizing the database performance of a Laravel application is to effectively measure its performance. Several tools can help with this, with the most common being Laravel Debug Bar. Additionally, I highly recommend using Laravel Telescope for deeper insights.
Here are three key metrics to monitor when using Laravel Debug Bar or Telescope to optimize database performance:
If a Laravel 12 application is consuming significantly more than 1MB of RAM per request, inefficient database queries are often the culprit.
Query execution time and duplicates (or N+1) queries are the most important metrics that help you understand the performance of your Laravel application.
Sometimes, your database queries may be fast and free of duplicates but still return a large number of Eloquent models. This can lead to high memory usage in your application. A common solution is to offload heavy logic operations to the database layer.
This is the second item Jonathan pointed out in his course. We have to make sure we minimise the memory usage of our database queries by selecting only the columns we from the database.
✅ DO
Post::get(columns: ['id', 'title', 'published_at']);
Post::find(id: 1, columns: ['id', 'title', 'body', 'published_at']);❌ DONT
Post::get();
Post::find(id: 1);Subqueries are queries that run inside another query as a single query. We can use subqueries to pull additional information from other tables or compute derived values.
When optimizing a database query for performance, the focus is on keeping both the number of database queries and memory usage to a minimum. A good starting point for minimizing query count is solving N+1 queries. The next step is reducing the memory usage of database queries, and that's where subqueries come to the rescue.
For example, let's say we want to load the last time a user logged into our application. The default solution is to define a HasMany relationship between the User model and the Login model, which records the user's login activities.
class User extends Model
{
public function loginActivity(): HasMany
{
return $this->hasMany(Login::class)
}
}Now, we can eagerly load the login activities and sort them to retrieve the latest one.
❌ DONT
$user = User::with('loginActivity')->first();
$lastLoginAt = $user->loginActivity->sortByDesc('created_at')->first()->created_at;While this seems to solve our problem, there will be a memory issue when dealing with a collection of users or loading a user with thousands of login activities. This is because we are eagerly loading all the login activities of a user and storing them in memory. The more users we load, the more activities we store in memory.
We can solve this memory issue using subqueries. Subqueries can only return one column, which means we can load the latest login date as a column on the User model using Eloquent's addSelect() method. Since we're only returning one column, we need to order the login activities by the latest and take the first record.
✅ DO
$user = User::addSelect(['last_login_at'=> Login::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])->first();
$lastLoginAt = $user->last_login_at;With this subquery, we have eliminated the eager load query, and we no longer need to load all the login activities of a user. Now, even when we are loading multiple user records, it will still be just a single query pulling records from two different tables.
With our last login implementation, we are only able to work with a single column from the Login table. If we want to work with more columns from the Login table, then our subqueries won't be enough, as we would have to keep adding more subqueries for every new column we want to retrieve.
For example, if we need more information like the login IP address, device type, and browser, our query will look like this:
❌ DONT
$user = User::query()
->addSelect(['last_login_at'=> Login::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])
->addSelect(['ip_address'=> Login::select('ip_address')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])
->addSelect(['browser'=> Login::select('browser')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])
->first();
$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;Even if we decide to extract this as a scope, it still makes it a chore each time we add additional information from the last login activity record.
With a subquery, we can define a dynamic relationship between the Login and User model. This relationship is dynamic because the column that connects these two tables is defined using a subquery at runtime.
For example, now that we want to load the last login as a model, we have to define a relationship in our User model that will return a Login model, and that will be a BelongsTo relationship.
class User extends Model
{
public function lastLoginActivity(): BelongsTo
{
return $this->belongsTo(Login::class)
}
}For this relationship to work, our users table must have last_login_id as foreign key column. However, we can use a subquery to provide that column without having to normalize the users table. With a subquery, we can provide the foreign key column, and with eager loading, we can load our dynamic relationship lastLoginActivity using the last_login_id column provided by the subquery. It looks like this:
✅ DO
$user = User::addSelect(['last_login_id'=> Login::select('id')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])
->with('lastLoginActivity')
->first();
$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;With Eloquent scope, we can clean up our subqueries and make them reusable. Let's start by adding a scope on our User model for the last_login_at column in our first subquery example.
class User extends Model
{
public function scopeWithLastLoginAt(Builder, $query): BelongsTo
{
$query->addSelect(['last_login_at'=> Login::select('created_at')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
]);
}
}Now, we use this scope whenever we want to include the last_login_at column for a user. Like this:
$user = User::withLastLoginAt()->first();
$lastLoginAt = $user->last_login_at;For our second subquery example, we can do the same thing by creating a scope that eager loads our lastLoginActivity dynamic relationship like this:
class User extends Model
{
public function scopeWithLastLoginActivity(Builder, $query): BelongsTo
{
$query->addSelect(['last_login_id'=> Login::select('id')
->whereColumn('user_id', 'users.id')
->latest()
->take(1)
])->with('lastLoginActivity');
}
}Now, we can load our dynamic relationship like this:
$user = User::withLastLoginActivity()->first();
$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;Chaperone()All relationships point to each other, which is another way of saying that all relationships are circular. For example, if we have a Book and a Category model, we know that a book belongs to a category, and a category could have many books.
Circular relationships usually result in N+1 queries, which happens when we try to access the parent model from a child model while looping through the child models, like this:
$categories = Category::with('books')->get();
foreach ($categories as $category) {
foreach ($category->books as $book) {
echo $book->category->title; // N+1 queries because we are lazy loading the category relas.
}
}Now we have another N+1 query problem because we are lazy loading category, the parent relationship of our Book model.
Laravel 11 introduced a chaperone() method that solves this problem by hydrating the parent models on the children without loading them from the database again. We can call the chaperone() method at runtime like this:
$categories = Category::with(['books' => fn ($books) => $books->chaperone()])->get();We can also call the chaperone() method when we define the child relationship. In our book category example, it looks like this:
class Category extends Model
{
public function books(): HasMany
{
return $this->hasMany(Book::class)->chaperone();
}
}Indexes are special lookup tables used by the database search engine to speed up data retrieval.
Column indexing is a concept that improves the performance of any application connected to a database, and Laravel provides an elegant way to add indexes, but it doesn't tell us where and when to add one.
The basic principle of indexing is to always index columns that are frequently queried, used for sorting and ordering, used in WHERE clauses, and used in JOIN statements.
Let me show you a few things you need to look out for when thinking about indexing.
The EXPLAIN statement provides information about how the database engine executes queries, including where we should add indexes to a table to make the query execute faster.
Laravel's Query & Eloquent builders provide an explain() method that allows us to dump the SQL query and examine its execution. Here is an example:
Category::select(['id', 'name'])->explain()->dd();The output looks like this:
array:1 [
0 => {#3708
+"id": 1
+"select_type": "SIMPLE"
+"table": "categories"
+"partitions": null
+"type": "ALL"
+"possible_keys": null
+"key": null
+"key_len": null
+"ref": null
+"rows": 29
+"filtered": 10.0
+"Extra": "Using where"
}
]From the array output of our EXPLAIN statement, we need to pay attention to the possible_keys item, which shows if any indexed column could be used, and the key item, which shows the indexed column that was actually used in the query. This command is also availabe for both MySQL and PostgreSQL.
Multi-column indexing is very effective when multiple columns are queried in the same WHERE clause. For example, when performing a search on the first_name and last_name columns of our User model, the query might look like this:
$search = 'Jude';
$result = User::where(
fn (Builder $query) => $query
->where('first_name', "$search%")
->orWhere('last_name', "$search%")
)->get();Defining individual indexes on these columns will have no effect on this query. To optimize such a query, we need to create a multi-column index on the columns in the WHERE clause—in this case, the first_name and last_name columns. We need to do this inside our users table migration file, like this:
// migration
Schema::create('users', function (Blueprint $table) {
...
$table->index(['first_name', 'last_name']);
});Now that we’ve created a multi-column index, it’s important to note that the WHERE clause must follow the exact order in which the index was created. For example, if we reverse the WHERE clause to start with the last_name column, our multi-column index may not be used effectively.
When querying multiple tables in a single SELECT query, such as a search query that matches a term in two different tables, using UNION can produce faster results. This is because a UNION query consists of individual queries, each of which can take advantage of the indexes on the individual tables.
For example, if we have an employees table and a companies table with over 100,000 records, where each employee belongs to a company, and we want to perform a search to match either the employee's name or the company's name (with search columns indexed respectively), our first approach, using subqueries, would look like this:
❌ DONT
$searchTerm = 'taylor';
Employee::with('company')
->where(fn($query) => $query
->where('name', 'LIKE', "$searchTerm%")
->orWhereIn('company_id', fn($query) => $query
->select('id')
->from('companies')
->where('name', 'LIKE', "$searchTerm%")))
->get()The problem with our subquery approach is that it doesn't make use of only the employee's name column index on the employees table, which in turn makes our query slow because the subquery for the company's name search is not make use of the name column index. Testing this over 200,000 records on my 2021 M1 MacBook Pro, these queries—although they are just two queries—took over 300ms.
Our second approach would be to run these queries independently. Instead of using a subquery, we would run a separate query for the company's name search and append the result to our employee query like this:
✅ DO
Employee::with('company')
->where(fn($query) => $query
->where('name', 'LIKE', "$searchTerm%")
->orWhereIn('company_id', Company::where('name', 'LIKE', "$searchTerm%")->pluck('id'))) // independent query
->get()This query performs better than our first query because it leverages the employee's name column index, while the independent query for the employee's company name search also benefits from the name column index on the companies table. However, this approach introduces a third query, even though it improved our response time, reducing it from over 300ms to 4ms.
Our third approach is to use UNION to run this query independently as a single operation. The goal is to reduce the number of queries while making use of the indexes defined on these columns. Our UNION query looks like this:
✅ DO
$employeeQuery = DB::query()
->select('id')
->from('employees')
->where('name', 'LIKE', "$searchTerm%");
$companyQuery = DB::query()
->select('employees.id')
->from('employees')
->join('companies', 'companies.id', '=', 'employees.company_id')
->where('companies.name', 'LIKE', "$searchTerm%");
Employee::with('company')
->whereIn('id', fn (Builder $query) => $query
->select('id')
->from(table: $employeeQuery->union($companyQuery), as: 'matches'))
->get();With our UNION query, we've reduced the number of queries to two, and all of our indexes are being utilized. Most importantly, we've improved our response time, reducing the query duration from 4ms to 2ms.
To avoid N+1 queries in your Laravel applications, especially during development, we can disable lazy loading. This can be done in a way that doesn't affect the production environment by adding the snippet below to the AppServiceProvider's boot method:
public function boot()
{
Model::preventLazyLoading(! app()->isProduction());
}This will throw an exception whenever lazy loading is introduced in our queries during development.
You can reach out to me on X(formerly Twitter) if you have any questions.
Be the first to hear about anything I publish, launch, or think is helpful for you. Subscribe here
Litehost is a web hosting platform for PHP & Laravel developers with Composer, Git, PHP & CLI pre-installed. Try it now