DEV Community

Cover image for How to Optimize Database Queries in Laravel?
Chetan Rohilla
Chetan Rohilla

Posted on • Originally published at readymadecode.com

How to Optimize Database Queries in Laravel?

Some times to run larger laravel applications smoothly we needs to optimize our laravel application. One of these tasks here is how to optimize database queries in laravel.

1. Retrieving Large Data Records

Option 1: Using chunk

If your application required to process larger number of records. Then you should get the records in chunks. The below example retrieves 100 records from the posts table, processes them, retrieves another 100 records, and processes them. Chunk retrieves based on offset and limit.

// 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
    }
});
Enter fullscreen mode Exit fullscreen mode

Option 2: Using chunkById

You can also use chunkById. As chunkById is using the id field which is an integer, and the query is using a where clause, the query will be much faster.

// 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
    }
});
Enter fullscreen mode Exit fullscreen mode

Option 3: Using cursor

It will make a single database query, retrieve all the records from the table, and saturate the Eloquent models one by one. Since we are retrieving all the entries from a table, the memory usage on the database instance will still be higher. You can use a cursor If your web app running your application has less memory, and the database instance has more memory.

// 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
} 
Enter fullscreen mode Exit fullscreen mode

2. Select only the columns which you need

You should use select keyword in your query to select only specific columns instead of fetching all.

$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
Enter fullscreen mode Exit fullscreen mode

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

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

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

When the above code is executed, it does the following behind the scenes.

Executes select title, slug from posts query on the database
Creates an array with title as array value and slug as array key.
Returns the array(array format: [ slug => title, slug => title ])

Now, to access the results, we would do

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

If you want to retrieve only one column, you can do

$posts = Post::pluck('title'); //When using eloquent
$posts = DB::table('posts')->pluck('title'); //When using query builder
foreach ($posts as  $title){
    // $title is the title of a post
}
Enter fullscreen mode Exit fullscreen mode

4. Count rows using a query instead of a collection

Using this approach we can directly count the total no of rows on the database itself.

$posts = Post::count(); //When using eloquent
$posts = DB::table('posts')->count(); //When using query builder
Enter fullscreen mode Exit fullscreen mode

5. Using with in relationships

If you are using relationships in your eloquent mode then you should use with method.

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

Similarly, if you are using nested relationship then you can use this

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

6. Do not call unnecessary relationship

Do not call relationship If you are sure the id of the relationship is present in table.

Example:

To get the author id of a post, we would normally do

$post = Post::findOrFail(<post id>);
$post->author->id;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

But always sure that a row always exists in authors table if it is referenced in posts table.

7. Add index to frequently queried columns

If you are quering a record on where condition to column. Then you can add index to that column. Queries are much faster when querying rows with an index column.

8. Use simplePaginate instead of Paginate

Paginate Generates pagination links for all the pages. And simplePaginate Generates only next and previous pagination links.

$posts = Post::paginate(20); // Generates pagination links for all the pages

$posts = Post::simplePaginate(20); // Generates only next and previous pagination links
Enter fullscreen mode Exit fullscreen mode

9. Avoid using leading wildcards(LIKE keyword)

If We know the keyword occurs at the beginning of the column value, We can query the results as below.

select * from table_name where column like keyword%
Enter fullscreen mode Exit fullscreen mode

10. Avoid using SQL functions in where clause

Simply use where on date columns as well like this

$posts = Post::where('created_at', '>=', now() )->get(); //Use this

$posts = POST::whereDate('created_at', '>=', now() )->get(); //Not this
Enter fullscreen mode Exit fullscreen mode

11. Avoid adding too many columns to a table

Adding too many columns to a table will increase the individual record length and will slow down the table scan. When you are doing a select * query, you will end up retrieving a bunch of columns which you really do not need.

12. Separate columns with text data type into their own table

Consider a case where you have a table called posts with a column of content which stores the blog post content.
The content for blog post will be really huge and often times, you need this data only if a person is viewing this
particular blog post.

So separating this column from the posts table will drastically improve the query performance when there are too many posts.

13. Better way to retrieve latest rows from a table

So the better way to retrieve latest rows is as below. Because it is ordering the records based on id field as compared to string field.

$posts = Post::latest('id')->get();
Enter fullscreen mode Exit fullscreen mode

14. Inspect and optimize queries

There is no one universal solution when optimizing queries in laravel. Only you know what your application is doing, how many queries it is making, how many of them are actually in use.

To do this you can use

DB::enableQueryLog();
dd(DB::getQueryLog());
Enter fullscreen mode Exit fullscreen mode
  1. Laravel Debugbar - Laravel debugbar has a tab called database which will display all the queries executed when you visit a page. Visit all the pages in your application and look at the queries executed on each page.

  2. Clockwork - Clockwork is same as laravel debugbar. But instead of injecting a toolbar into your website, it will display the debug information in developer tools window or as a standalone UI by visiting yourappurl/clockwork.

  3. Laravel Telescope - Laravel telescope is a wonderful debug companion while developing laravel applications locally. Once Telescope is installed, you can access the dashboard by visiting yourappurl/telescope. In the telescope dashboard, head over to queries tab, and it will display all the queries being executed by your application.

15. Optimize MySQL inserts

Use a server backed by performant SSD storage

Make sure the disk for your server is as fast as possible.

Increase the log file size limit

The default innodb_log_file_size limit is set to just 128M, which isn't great for insert heavy environments. Increasing this to something larger, like 500M will reduce log flushes (which are slow, as you're writing to the disk). This is particularly important if you're inserting large payloads.

Defer disk writes by flushing the log less frequently

By setting innodb_flush_log_at_trx_commit to 2, we were able to drop the insert time average from over 200ms to under 50ms.

You can increase the flush interval by setting innodb_flush_log_at_timeout to your desired interval (in seconds).


Thanks For Reading :)
Please give your opinion in comments :)
This is my website where you can Find More ReadyMadeCode

Discussion (4)

Collapse
xorock profile image
xorock

What's the best (fastest) method to add the same value to entire collection? Something like
$date=now();
$posts = Post::get();//[['title'=>'x'], ['title'=>'y']]
And now after merge
[['title'=>'x', 'date' => '2021...'], ['title'=>'y',, 'date' => '2021...']]

Collapse
readymadecode profile image
Chetan Rohilla Author

You can use
$posts = Post::where('date','<',now())->whereIn('title',['x','y'])->get();
the condition on date can be < or > or <= or >=

Collapse
xorock profile image
xorock

I don't want to filter results. I want to add one column (like id from external query) to all results. I can do:
Post::get()->map(function($item) use ($now) {
return $item->toArray() + ['date' => $now];
});
But, is there a better solution?

Thread Thread
readymadecode profile image
Chetan Rohilla Author

Try this

$posts = Post::all()->map(function ($post) use($custom_value) {
// modify eloquent object here
$post->custom_column = $custom_value;
//apply any condition if available
return $post;
});