Hey developers! If you’re like me, constantly striving to make your Laravel applications faster and more efficient, you’re in for a treat. In this guide, I’m excited to share 11+ game-changing Laravel tips to supercharge your database queries as we step into 2024.
Database optimization doesn’t have to be a head-scratcher, and with these simple tips, we’ll explore ways to enhance your Laravel projects, ensuring they not only run smoothly but also deliver top-notch performance.
In this article, we’ll see 11+ laravel tips: optimize database queries (2024), the best 11 tips and tricks to improve database queries in laravel 8/9/10, and query optimization in laravel 2024.
Ready to dive into the world of optimized database queries? Let’s make our Laravel applications faster and more responsive together.
1. Minimizing Unnecessary Queries
Sometimes, we end up running database queries that aren’t really needed. Take a look at the example below.
<?php
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 provided code fetches rows from two distinct tables (e.g., "posts" and "private_posts") and then sends them to a view. Take a peek at the corresponding view file presented below.
// posts/index.blade.php
@if( request()->user()->isAdmin() )
<h2>Private Posts</h2>
<ul>
@foreach($private_posts as $post)
<li>
<h3></h3>
<p>Published At: </p>
</li>
@endforeach
</ul>
@endif
<h2>Posts</h2>
<ul>
@foreach($posts as $post)
<li>
<h3></h3>
<p>Published At: </p>
</li>
@endforeach
</ul>
As you can see above, $private_posts is visible to only a user who is an admin. Rest all the users cannot see these posts.
We can modify our logic below to avoid this extra query.
$posts = Post::all();
$private_posts = collect();
if( request()->user()->isAdmin() ){
$private_posts = PrivatePost::all();
}
2. Consolidate Similar Queries for Improved Efficiency
Sometimes, we find ourselves needing to create queries to fetch various types of rows from a single table.
$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();
Instead of this 3 different queries:
$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');
3. Optimizing Performance: Adding Index to Frequently Queried Columns
When you’re filtering queries using a condition on a text-based column, it’s a smart move to slap an index on that column. Why? Because adding an index makes your queries way speedier when sifting through rows.
Think of it like a well-organized filing system – it just makes finding what you need a whole lot faster!
$posts = Post::where('status','=','published')->get();
In the example above, we’re fetching records based on a condition added to the "status" column. To boost the query’s performance, consider enhancing it with the following database migration.
Schema::table('posts', function (Blueprint $table) {
$table->index('status');
});
4. Optimize Pagination: Switch to simplePaginate Over Paginate
When it comes to paginating results, our typical approach would be:
$posts = Post::paginate(10);
When using pagination in Laravel, the typical approach involves two queries: one to retrieve paginated results and another to count the total number of rows in the table. Counting rows can be slow and impact query performance.
But why does Laravel count the total number of rows?
It does so to generate pagination links. By knowing the total number of pages beforehand, along with the current page number, Laravel facilitates easy navigation. You can jump to any page with confidence.
On the flip side, using simplePaginate skips the total row count, making the query faster. However, you sacrifice the knowledge of the last page number and the ability to jump to specific pages.
For large database tables, favor simplePaginate over paginate can significantly improve performance.
$posts = Post::paginate(20); // Generates pagination links for all the pages
$posts = Post::simplePaginate(20); // Generates only next and previous pagination links
5. Optimizing Database Queries: Avoiding Leading Wildcards with the LIKE Keyword
When aiming to retrieve results that match a particular pattern, our usual go-to approach is to use:
select * from table_name where column like %keyword%
The previous query scans the entire table, which can be inefficient. If we’re aware that the keyword appears at the start of the column value, a more efficient query can be formulated as follows:
select * from table_name where column like keyword%
6. Optimizing WHERE Clauses: Minimizing the Use of SQL Functions
It’s advisable to steer clear of using SQL functions in the WHERE clause, as they can lead to a full table scan. Take a peek at the example below: when querying results based on a specific date, the typical approach involves:
$posts = POST::whereDate('created_at', '>=', now() )->get();
This will result in a query similar to below.
select * from posts where date(created_at) >= 'timestamp-here'
The initial query causes a full table scan because the where condition isn’t applied until the date function is evaluated.
To improve this, we can restructure the query to eliminate the need for the date SQL function, as shown below:
$posts = Post::where('created_at', '>=', now() )->get();
select * from posts where created_at >= 'timestamp-here'
7. Optimizing Table Structure: Minimizing the Addition of Excessive Columns
To enhance performance, it’s wise to keep the number of columns in a table to a minimum. In databases like MySQL, you can optimize by breaking down tables with numerous columns into multiple tables. These tables can then be linked using primary and foreign keys.
Including excessive columns in a table extends the length of each record, leading to slower table scans. This becomes evident when executing a "select *" query, as it fetches unnecessary columns, causing a slowdown in retrieval speed
8. Separating Columns with Text Data Type into Their Own Table
When dealing with tables that store substantial data, especially in columns like TEXT, it’s wise to consider separating them into their own table or into a less frequently accessed table.
This practice proves beneficial because columns with extensive information can significantly inflate the size of individual records, impacting query times.
For instance, picture a table named "posts" with a "content" column storing hefty blog post content. Given that this detailed content is typically required only when someone is viewing that specific blog post, extracting this column from the main "posts" table can dramatically enhance query performance, especially when dealing with a multitude of posts.
9. More Efficient Method for Retrieving the Latest Rows from a Table
When we aim to fetch the most recent rows from a table, our usual approach often involves the following:
$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
Instead of this, you can do like this:
$posts = Post::latest('id')->get();
// or $posts = Post::orderBy('id', 'desc')->get();
select * from posts order by id desc
10. Optimizing MySQL Inserts
So far, we’ve focused on making select queries faster for fetching data from a database. Usually, our attention revolves around optimizing read queries. Yet, there are instances where we need to speed up insert and update queries as well.
// Instead of inserting records one by one like this:
foreach ($data as $record) {
DB::table('your_table')->insert($record);
}
// You can optimize it by using the insert method with an array of data like this:
DB::table('your_table')->insert($data);
11. Inspecting and Optimizing Queries
When it comes to optimizing queries in Laravel, there’s no one-size-fits-all solution. After all, who knows your application better than you do? Understanding its behavior, the number of queries it churns out, and which ones are necessary is key.
By inspecting these queries, you gain valuable insights and can work towards reducing their overall number.
To aid in this crucial task, several tools are available to help you scrutinize queries on every page.
However, a word of caution: refrain from running these tools in your production environment. Doing so might compromise your application’s performance and expose sensitive information to unauthorized users.
Here are a few tools to inspect and optimize your queries:
-
Laravel Debugbar:
- Laravel Debugbar features a handy "database" tab, revealing all executed queries when you navigate through your pages. Visit each page in your application to observe the queries in action.
-
Clockwork:
- Similar to Laravel Debugbar, Clockwork provides debug information. However, instead of injecting a toolbar into your website, it displays the details in the developer tools window or as a standalone UI accessible at yourappurl/clockwork.
-
Laravel Telescope:
- Laravel Telescope serves as an excellent debugging companion during local Laravel development. Once installed, access the dashboard by visiting yourappurl/telescope. Navigate to the "queries" tab to view and analyze all the queries executed by your application.
Remember, these tools are best suited for your development environment to fine-tune your queries without risking your production’s performance and security.
Happy optimizing!
You might also like:
