7+ Laravel Tips: Optimize Database Queries (2024)

Hey there, fellow developers! If you've been navigating the intricate world of Laravel, you know that optimizing database queries is a crucial aspect of crafting high-performance web applications. As we dive into 2024, the demand for faster, more efficient systems is greater than ever.

That's why I've put together this article, focusing on 7+ invaluable Laravel tips to help you supercharge your database queries.

In this article, we'll see 7+ laravel tips to optimize database queries, optimize queries in laravel 8/9/10, laravel 10 query optimization 2024, and improve laravel performance 2024.

Ready to boost your application's performance? Let's get started!

This tip primarily targets the enhancement of your application's memory usage, especially when handling extensive datasets.

When faced with the task of processing a substantial number of records, it's often more efficient to avoid fetching the entire set in one go. Instead, consider retrieving a subset of results and processing them in manageable groups.

Typically, when fetching a large set of results from a table, such as posts, the conventional approach involves querying all records simultaneously, as demonstrated below.

$posts = Post::all(); // when using eloquent
$posts = DB::table('posts')->get(); // when using query builder
 
foreach ($posts as $post){
 // Process posts
}

The examples above demonstrate the retrieval of all records from the "posts" table for subsequent processing. However, imagine dealing with a table that boasts 1 million rows – the potential memory exhaustion becomes a significant concern.

To preemptively tackle challenges associated with handling such massive datasets, it is prudent to adopt a strategy of retrieving and processing results in smaller, more manageable subsets, as illustrated below.

Method 1: Using a chunk

// when using eloquent
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 
// when using query builder
$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

Method 2: Using cursor

// when using eloquent
foreach (Post::cursor() as $post){
   // Process a single post
}
 
// when using query builder
foreach (DB::table('posts')->cursor() as $post){
   // Process a single post
}

Method 3: Using chunkById

// when using eloquent
$posts = Post::chunkById(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 
// when using query builder
$posts = DB::table('posts')->chunkById(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

Typically, to fetch results from a database table, we would follow the steps outlined below.

$posts = Post::find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); //When using query builder

The above code will result in a query as below.

select * from posts where id = 1 limit 1

As evident in the query, the use of "SELECT *" implies fetching all columns from the database table. While this approach is acceptable when every column is essential, it becomes inefficient if only specific columns, such as "id" and "title," are required.

To optimize the query and conserve resources, we can specify the exact columns we need, as demonstrated below.

$posts = Post::select(['id','title'])->find(1); //When using eloquent

$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first(); //When using query builder

The above code will result in a query as below.

select id,title from posts where id = 1 limit 1

Utilize the "pluck" method for extracting precisely one or two columns from the database.

posts = Post::select(['title','slug'])->get(); //When using eloquent

$posts = DB::table('posts')->select(['title','slug'])->get(); //When using query builder

When you run the provided code, here's what happens:

1. It secretly runs a database query, specifically "select title, slug from posts."
2. For each row it finds, it generates a fresh Post model object (or a PHP standard object if using query builder).
3. These objects are grouped together to form a new collection.
4. Finally, the collection is handed back to you.

To see the results, you can access them like this:

foreach ($posts as $post){
    // $post is a Post model or php standard object
    $post->title;
    $post->slug;
}

The method mentioned above comes with an extra step: it creates a Post model for every single row and forms a collection with these objects.

This is useful if you genuinely require the Post model instances.

However, if all you're after is those two values, here's a simpler way to do it.

$posts = Post::pluck('title', 'slug'); //When using eloquent

$posts = DB::table('posts')->pluck('title','slug'); //When using query builder

When you run the provided code, here's what happens:

1. It asks the database for the titles and slugs from the posts.
2. It organizes this information into an array, where slugs are keys and titles are values.
3. The result is an array in the format [slug => title, slug => title].

To check the results, you can access them like this:

foreach ($posts as $slug => $title){
    // $title is the title of a post
    // $slug is the slug of a post
}

To find the total number of rows in a table, we usually use the following method:

$posts = Post::all()->count(); //When using eloquent

$posts = DB::table('posts')->get()->count(); //When using query builder

This will generate the following query.

select * from posts

The first method fetches all rows from the table, loads them into a collection, and then counts the results. It's okay with a small number of rows, but as the table size increases, we'll run out of memory.

A better way is to count the total number of rows directly in the database.

$posts = Post::count(); //When using eloquent

$posts = DB::table('posts')->count(); //When using query builder

This will generate the following query.

select count(*) from posts

You've probably heard this tip a lot, so I'll keep it short and straightforward.

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
}

posts/index.blade.php

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
    </li>
@endforeach

The code above fetches all the posts and shows their titles along with authors on a webpage, assuming there's an author relationship in your post model.

This code triggers the execution of the following queries.

select * from posts // Assume this query returned 5 posts
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

In the example, we used one query to get posts and five additional queries to fetch the authors of those posts (assuming we have five posts). Essentially, for each post retrieved, a separate query is made to get its author.

This scenario leads to the N+1 query problem, where for N posts, there are N+1 queries (1 query to retrieve posts and N queries to get the author for each post).

To tackle this issue, we can optimize by eagerly loading the author's relationship on posts, as demonstrated below.

$posts = Post::all(); // Avoid doing this

$posts = Post::with(['author'])->get(); // Do this instead

Executing the above code will result in running the following queries.

select * from posts // Assume this query returned 5 posts

select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

In the example above, imagine the author is part of a team, and you want to show the team name too. In the blade file, you'd do it like this.

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author's Team: {{ $post->author->team->name }}</p>
    </li>
@endforeach

Now doing the below.

$posts = Post::with(['author'])->get();

This will result in the following queries.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

In simple terms, despite eagerly loading the author's relationship, additional queries are being made because the team relationship on authors is not being loaded eagerly.

To resolve this, we can fix it by following the steps below.

$posts = Post::with(['author.team'])->get();

Executing the above code will result in running the following queries.

select * from posts // Assume this query returned 5 posts

select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

Imagine you have two tables, "posts" and "authors." In the "posts" table, there's a column called "author_id" representing a connection to the "authors" table using the belongsTo relationship.

To obtain the author's ID for a post, the usual approach would be to do:

$post = Post::findOrFail(1);
$post->author->id;

This would result in two queries being executed.

select * from posts where id = <post id> limit 1
select * from authors where id = <post author id> limit 1

Instead, you can directly get the author id by doing the following.

$post = Post::findOrFail(<post id>);

$post->author_id; // posts table has a column author_id which stores id of the author

You might also like:

• Read Also: How to Search with Pagination in Laravel 10 Vue 3
• Read Also: How To Backup Database In Laravel 9 Using Spatie
• Read Also: Laravel 9 Insert Multiple Records In Database
• Read Also: How To Store JSON Data In Database Laravel 9