How Do We Optimize Laravel Database Queries?

If your application is running slowly or doing a large number of database queries, follow the performance optimization tips below to improve your application load time.

1. Import large datasets

This tip mainly focuses on improving your application's memory usage when dealing with large datasets.

If your application needs to process a large dataset, instead of importing it all at once, you can import a subset of the results and process them in groups.

To retrieve a large set of results from a table Posts, we usually do the following.

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

The examples above will import and process all records in the post table. What if there are 1 million rows in this table? You'll easily run out of ram.

To avoid problems when dealing with a largedataset,you can import a subset of the results and process them as follows.

// 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
}
});

The example above retrieves, processes, retrieves, and processes 100 other records from the post table. This cycle will continue until all records are processed.

This approach will make more database queries, but will make memory quite efficient. Typically, large datasets will be processed in the background. Therefore, it is ok to make more queries when running in the background to prevent running out of memory when processing large datasets.

// 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
}

The example above will query a single database, import all records from the table, and process meaningful models individually. This approach will only make a database query to receive all shipments. However, it uses php generators to optimize memory usage.

when can you use this

Although this greatly optimizes application-level memory usage, memory usage in the database instance will still be higher because we receive all inputs in a table.

Cursor is better to use if your web application running your application has less memory and the database instance has more memory. However, if your database instance does not have enough memory, it is better to use chunk.

Note: This feature is only available on laravel 8 and above.

// 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
}
});

Chunkand chunkById The big difference between them idfield-based database results. This ID field is usually an integer field, and in most cases it will automatically be an incremented field.

Made by queries chunkand chunkByIdas follows.

Chunk

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

chunkById

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

Usually the limit and offset are slower and try to avoid using them. Because ChunkById uses an id field, which is an integer field, and uses a query where clauseThe query will be much faster.

When can you use chunkById? — If your application is running laravel 8 or later — in your database table idyou can use it if there is a column with an integer field.

2. Select only the columns you need

We usually do the following to get results from a database table.

$posts = Post::find(1); 
$posts = DB::table('posts')->where('id','=',1)->first();

The above code will result in a query as follows

select * from posts where id = 1 limit 1

As you can see, the query is a select *. This means that it has received all columns from the database table. If we really need all the columns in the table, that's fine.

But instead, if we only need certain columns (id, title), we can only get these columns, as below.

$posts = Post::select(['id','title'])->find(1); $posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first();

The above code will result in a query as follows

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

3. Use pluck when you need exactly one or two columns from the database

This tip focuses more on the time spent after the results are retrieved from the database. This does not affect the actual query duration.

$posts = Post::select(['title','slug'])->get(); $posts = DB::table('posts')->select(['title','slug'])->get();

When the above code is run, it does the following in the background.

  • select title, slug from postsExecutes a query in a database
  • Receive Postcreates a new model object for each row (Creates a PHP standard object for the query builder)
  • PostCreates a new collection with models
  • Returns the collection

Now, let's get the results.

foreach ($posts as $post){
$post->title;
$post->slug;
}

The above approach is Posthas the overhead of modeling for each row and creating a collection for these objects. PostThis will be best if you really need the model. But if all you need are these two values, you can do the following.

$posts = Post::p luck('title', 'slug'); $posts = DB::table('posts')->pluck('title','slug');

When the above code is run, it does the following in the background.

  • select title, slug from postsRuns the query in the database.
  • Creates an array array key.

Now, we'd do it to get the results.

foreach ($posts as $slug => $title){
$title
$slug
}

If you only want to import one column, you can:

$posts = Post::pluck('title'); $posts = DB::table('posts')->pluck('title'); foreach ($posts as $title){
$title
}

The above approach Posteliminates the creation of objects for each row. This reduces memory usage and time spent processing query results.

4. Count rows using query instead of collection

To count the total number of rows in a table, you can normally

$posts = Post::all()->count(); $posts = DB::table('posts')->get()->count();

this will generate the following query

select * from posts

The above approach will take all rows from the table, making them collectionobject and count the results. This works well when there are fewer rows in the database table. But as the picture grows, our memory will quickly run out.

Instead of the above approach, we can directly count the total number of rows in the database itself.

$posts = Post::count(); $posts = DB::table('posts')->count();

this will generate the following query

select count(*) from posts

Counting rows in SQL is a slow process and performs very poorly when there are too many rows in the database table. It is better to avoid counting rows as much as possible.

5. Avoid N+1 queries with willing installation relationships

You may have heard this clue a million times. So I'm going to keep it as short and simple as possible. suppose you have the following scenario

class PostController extends Controller
{
public function index()
{
$posts = Post::all();
return view('posts.index', ['posts' => $posts ]);
}
}
// posts/index.blade.php file@foreach($posts as $post)

  • {{ $post->title }}


    Author: {{ $post->author->name }}



  • @endforeach

    The above code is to receive all posts and view the title and author of the article on the web page. The above code authorassumes that you have a relationship with your post model.

    Executing the above code will cause the following queries to run.

    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 }

    As you can see, we have a query to receive the posts and 5 queries to get the authors of the posts (assuming we have 5 submissions) So for each post it receives, it makes a separate query to get its author.

    Therefore, if there are N-number of posts, N+ makes 1 query (1 query to receive the posts and N query to get the author for each post). This is commonly known as the N+1 query problem.

    To avoid this, upload the author's relationship willingly to publications as follows.

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

    Executing the above code will cause the following queries to run.

    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 } )

    6. Willing load nested relationship

    In the example above, consider that the author belongs to a team and you also want to show the team name. So in the blade file, you do as follows.

    @foreach($posts as $post)

  • {{ $post->title }}


    Author: {{ $post->author->name }}


    Author's Team: {{ $post->author->team->name }}



  • @endforeach

    Now we do it like this on the controller.

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

    It 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 }

    As you can see, authorsWhile we're willing to upload the relationship, he's still making more inquiries. because Teamwe are not willing to upload the relationship.

    We can fix this by doing the following.

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

    Executing the above code will cause the following queries to run.

    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 } )

    Therefore, by loading the nested relationship, we reduced the total number of queries from 11 to 3.

    7. Do not install the OwnTo relationship, just install it if you need id

    Suppose you have two tables Postsand authors. In the writing table, author_idThe authors table contains a column that represents an OwnTo relationship.

    To get the author ID of a post, we would normally do as follows

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

    This causes two queries to be executed.

    select * from posts where id =  limit 1
    select * from authors where id = limit 1

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

    $post = Post::findOrFail();  $post->author_id;

    When can I use the above approach?

    You can use the above approach when you are sure that a row always exists in the Authors table.

    8. Avoid unnecessary queries

    Most of the time, we make database queries that are not required. As in the following example.

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

    The above code is retrieving rows from two different tables Postsprivate_posts) and pass to view them. The View file looks like this.

    // posts/index.blade.php@if( request()->user()->isAdmin() )

    Private Posts



      @foreach($private_posts as $post)

    • {{ $post->title }}


      Published At: {{ $post->published_at }}



    • @endforeach

    @endif

    Posts



      @foreach($posts as $post)

    • {{ $post->title }}


      Published At: {{ $post->published_at }}



    • @endforeach

    As you can see above, $private_postsonly Admin not all users can see these posts.

    $posts = Post::all();
    $private_posts = PrivatePost::all();

    We're doing two interrogations. Someone records Postsfrom the table, the other records private_poststo retrieve from the table .

    private_postsRecords in a table can only be admin user. however, we are querying to get these records for all users.

    To avoid this extra query, you can do as follows.

    $posts = Post::all();$private_posts = collect();if( request()->user()->isAdmin() ){
    $private_posts = PrivatePost::all();
    }

    By changing our logic as above, we make two queries for the administrative user and one query for all other users.

    9. Combine similar queries

    Sometimes we need to make queries to retrieve different types of rows from the same table.

    $published_posts = Post::where('status',=','published')->get();
    $featured_posts = Post::where('status',=','featured')->get();
    $scheduled_posts = Post::where('status','=','scheduled')->get();

    The above code takes rows that are different from the same table. The code will cause the following queries to be performed.

    select * from posts where status = 'published'
    select * from posts where status = 'featured'
    select * from posts where status = 'scheduled'

    As you can see, he's doing three different queries to the same table to get the records back. We can rearrange this code to make only one database query.

    $posts = Post::whereIn('status',['published', 'featured', 'scheduled'])->get();$published_posts = $posts->where('status','=','published');
    $featured_posts = $posts->where('status','=','featured');
    $scheduled_posts = $posts->where('status','=','scheduled');
    select * from posts where status in ( 'published', 'featured', 'scheduled' )

    The above code is to make a single query to receive all shipments with any of the specified statuses and create separate collections for each state by filtering the returned shipments according to their status. Therefore, with their status, we will still have three different variables and we are only making one query.

    10. Use simplePaginate instead of paginating

    We usually use the following method when patizing results.

    $posts = Post::p aginate(20);

    This is going to do two interrogations. 1 to get paginated results, and the other to count the total number of rows in the table. Counting rows in a table is a slow process and adversely affects query performance.

    So why does laravel count the total number of rows?

    To create pagination links, Laravel counts the total number of rows. Therefore, when pagination links are created, you know in advance how many pages will be there and what the past page number is. So you can easily go to any page you want.

    On the other hand, simplePaginatedoes not count the total number of rows and the query paginatewill be much faster than the approach. However, you will lose the ability to know the last page number and switch to different pages.

    If your database table contains a large number of rows, paginateinstead simplePaginate it's better to do it.

    $posts = Post::p aginate(20); $posts = Post::simplePaginate(20);

    11. Avoid using leading wildcards (LIKE keyword)

    When trying to query results that match a particular model, you usually

    select * from table_name where column like %keyword%

    The above query will result in a full table scan. If we know that the keyword is at the beginning of the column value, we can question the results as follows.

    select * from table_name where column like keyword%

    12. Avoid using SQL functions in Where

    It is always better to avoid SQL functions in the where sentence, as it always results in a full table scan. Let's look at the example below. To query results by a specific date, you usually

    $posts = POST::whereDate('created_at', '>=', now() )->get();

    this will result in a query that is similar to the following

    select * from posts where date(created_at) >= 'timestamp-here'

    The query above dateit will result in a full table scan because the where condition is not applied until the function is evaluated.

    datewe can rearrange this to avoid sql functionality as follows

    $posts = Post::where('created_at', '>=', now() )->get(); select * from posts where created_at >= 'timestamp-here'

    13. Avoid adding too many columns to a table

    It is better to limit the total number of columns in a table. Relational databases, such as Mysql, can be used to divide multi-column tables into multiple tables. They can be combined using their primary and foreign keys.

    Adding too many columns to a table will increase the length of the individual record and slow down table scanning. one select *When you query, you receive a group column that you don't really need.

    14. Better way to retrieve the latest rows from a table

    When we want to retrieve the latest rows from a table, we usually

    $posts = Post::latest()->get();
    or $posts = Post::orderBy('created_at', 'desc')->get();

    The above approach will produce the following SQL query.

    select * from posts order by created_at desc

    The query basically sorts rows in descending order by created_at column. Because the created_at column is a string-based column, sorting results in this way is usually slower.

    If your database table has a primary key ID that automatically increases, in most cases the last row will always have the highest ID. Because the ID field is an integer field and also a primary key, it is much faster to sort the results by that key. Therefore, the better way to get the latest lines is as follows.

    $posts = Post::latest('id')->get();
    or $posts = Post::orderBy('id', 'desc')->get();
    select * from posts order by id desc

    15. Review and optimize queries

    There is no single universal solution when optimizing queries in Laravel. Only you know what your app does, how many queries it makes, how many are actually in use. Therefore, examining queries made by your application will help you determine and reduce the total number of queries made.

    You can use the following tool to help you review queries made on each page.

    • Laravel Debugbar — Laravel debugbar, one pagedatabasehas a tab that displays all queries executed when you visit. Visit all the pages in your app and look at the queries executed on each page.

      Quote

    KARABAY A, 2021 . How do we optimize Laravel Database Queries?

    https://www.karabayyazilim.com/blog/php/framework/laravel/laravel-veritabani-sorgularini-nasil-optimize-ederiz-2021-03-21-185044

    (Accessed March 21, 2021).


      Share this post

    Comments (1)

    • berat Reply

      Teacher, you've spilled it well. We'll wait for more. :)

      3 months ago

      Comment

      Subscribe
      Sign up for the email newsletter to be the first to know about my blog posts