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

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

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
} 

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

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

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
}

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
}

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

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

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

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

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;

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

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

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%

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

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();

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());

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 W3Courses