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:

1. 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.

2. 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.

3. 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:

hi, community.

Introducing #tomatophp, a cutting-edge open-source Laravel package designed to streamline development within the VILT stack environment. This innovative ecosystem leverages the power of Splade to effortlessly generate modern, high-performance single-page applications (SPAs) using only Blade files. #tomatophp redefines the development experience, making it both efficient and enjoyable.

As we progress, we continuously enhance our ecosystem by incorporating additional features into our plugins. Presently, we have developed a comprehensive e-commerce system using these plugins. In this article, we will guide you through the implementation process, demonstrating how you can seamlessly integrate a robust e-commerce system into your application.

Install Tomato Admin

you need to install tomato-admin plugin on your fresh Laravel app so let’s create a new Laravel app.

composer create-project laravel/laravel tomato

if you don’t have an environment for Laravel you can use this doc to build one on your Ubuntu Linux.

now cd inside your project folder change .env of your database and make sure that your Laravel app is running and the database is connected, you can check that by running migrations

php artisan migrate

now let’s start installing tomato-admin

composer require tomatophp/tomato-admin

after the composer is done run this command for auto-install

php artisan tomato-admin:install

if you are using macOS you can easily use auto yarn package install if not just build your assets like this

yarn & yarn build

now you have tomato-admin installed on your Laravel project.

we will use some media on our package to we need to publish Spatie Media Library migrations

php artisan vendor:publish --provider="Spatie\MediaLibrary\MediaLibraryServiceProvider" --tag="medialibrary-migrations"

if this command does not work please use this command

php artisan vendor:publish

then on the search type media and select the migration one.

now migrate your files

php artisan migrate

you can check your browser now and you will see a homepage like this

we need to change HOME const on the RouteServiceProvider.php to be /admin to make the redirect after auth to admin.

Install Tomato Roles

No e-commerce system is complete without a robust role management structure. To fulfill this essential requirement, we’ll be installing ‘tomato-roles’ to seamlessly handle roles within our mission to build a comprehensive e-commerce system.

composer require tomatophp/tomato-roles

after the composer is done run this command

php artisan tomato-roles:install

now go to your app\Models\User.php and add this trait to it

use \Spatie\Permission\Traits\HasRoles;

now your dashboard is ready to log in using admin@admin.com and password as a password from this URL /admin/login

if you try to access any page you will be redirected to Two-factor Confirmation If you don’t go it for now, you can easily stop it by removing implements MustVerifyEmail from your User.php model.

Install Tomato CRM

As an integral part of our e-commerce system, the management of customer interactions, authentications, and other crucial actions is paramount. To efficiently handle these aspects, we’ll be installing ‘tomato-crm.’ Let’s proceed with the installation to empower our system with advanced customer relationship management capabilities.

composer require tomatophp/tomato-crm

now let’s install it

php artisan tomato-crm:install

let’s publish Accounts.php model to our app to custom it

php artisan vendor:publish --tag="tomato-crm-model"

and you need to publish tomato-crm config

php artisan vendor:publish --tag="tomato-crm-config"

on tomato-crm.php config change the model path to like this

"model" => \App\Models\Account::class,

now we need to add a new guard to our app, so let’s add it on the config auth.php like this.

<?php

return [

    /*
|--------------------------------------------------------------------------
| Authentication Defaults
|--------------------------------------------------------------------------
|
| This option controls the default authentication "guard" and password
| reset options for your application. You may change these defaults
| as required, but they're a perfect start for most applications.
|
*/

    'defaults' => [
        'guard' => 'web',
        'passwords' => 'users',
    ],

    /*
|--------------------------------------------------------------------------
| Authentication Guards
|--------------------------------------------------------------------------
|
| Next, you may define every authentication guard for your application.
| Of course, a great default configuration has been defined for you
| here which uses session storage and the Eloquent user provider.
|
| All authentication drivers have a user provider. This defines how the
| users are actually retrieved out of your database or other storage
| mechanisms used by this application to persist your user's data.
|
| Supported: "session"
|
*/

    'guards' => [
        'web' => [
            'driver' => 'session',
            'provider' => 'users',
        ],
        'accounts' => [
            'driver' => 'session',
            'provider' => 'accounts',
        ]
    ],

    /*
|--------------------------------------------------------------------------
| User Providers
|--------------------------------------------------------------------------
|
| All authentication drivers have a user provider. This defines how the
| users are actually retrieved out of your database or other storage
| mechanisms used by this application to persist your user's data.
|
| If you have multiple user tables or models you may configure multiple
| sources which represent each model / table. These sources may then
| be assigned to any extra authentication guards you have defined.
|
| Supported: "database", "eloquent"
|
*/

    'providers' => [
        'users' => [
            'driver' => 'eloquent',
            'model' => App\Models\User::class,
        ],
        'accounts' => [
            'driver' => 'eloquent',
            'model' => App\Models\Account::class,
        ],
    ],

    /*
|--------------------------------------------------------------------------
| Resetting Passwords
|--------------------------------------------------------------------------
|
| You may specify multiple password reset configurations if you have more
| than one user table or model in the application and you want to have
| separate password reset settings based on the specific user types.
|
| The expiry time is the number of minutes that each reset token will be
| considered valid. This security feature keeps tokens short-lived so
| they have less time to be guessed. You may change this as needed.
|
| The throttle setting is the number of seconds a user must wait before
| generating more password reset tokens. This prevents the user from
| quickly generating a very large amount of password reset tokens.
|
*/

    'passwords' => [
        'users' => [
            'provider' => 'users',
            'table' => 'password_reset_tokens',
            'expire' => 60,
            'throttle' => 60,
        ],
    ],

    /*
|--------------------------------------------------------------------------
| Password Confirmation Timeout
|--------------------------------------------------------------------------
|
| Here you may define the amount of seconds before a password confirmation
| times out and the user is prompted to re-enter their password via the
| confirmation screen. By default, the timeout lasts for three hours.
|
*/

    'password_timeout' => 10800,

];

now clear your config cache

php artisan config:clear

now rebuild your assets

yarn & yarn build

now your CRM is ready you can check it on your dashboard

Install Tomato Wallet

For seamless transaction management between customers and vendors within the e-commerce system, a robust payment handler is crucial. Introducing ‘tomato-wallet,’ a feature-rich package that not only manages customer wallets but also seamlessly handles payments. Packed with a multitude of integrated payment gateways, ‘tomato-wallet’ ensures a magical experience in managing transactions. Let’s proceed with the installation to unlock the full potential of this powerful payment solution.

composer require tomatophp/tomato-wallet

let’s install it

php artisan tomato-wallet:install

now we need to implement Wallet interface to our Account.php and add HasWallet trait to it to make the wallet of the customer work, your Account model must be like this

<?php

namespace App\Models;

use Bavix\Wallet\Interfaces\Wallet;
use Bavix\Wallet\Traits\HasWallet;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;
use Spatie\Macroable\Macroable;
use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;
use Spatie\Permission\Traits\HasRoles;
use TomatoPHP\TomatoCrm\Models\Group;

/**
* @property integer $id
* @property string $name
* @property string $username
* @property string $loginBy
* @property string $address
* @property string $type
* @property string $password
* @property string $otp_code
* @property string $otp_activated_at
* @property string $last_login
* @property string $agent
* @property string $host
* @property integer $attempts
* @property boolean $login
* @property boolean $activated
* @property boolean $blocked
* @property string $deleted_at
* @property string $created_at
* @property string $updated_at
* @property AccountsMeta[] $accountsMetas
* @property Activity[] $activities
* @property Comment[] $comments
* @property Model meta($key, $value)
* @property Location[] $locations
*/
class Account extends Authenticatable implements HasMedia, Wallet
{
    use InteractsWithMedia;
    use HasApiTokens, HasFactory, Notifiable;
    use HasWallet;

    /**
* @var array
*/
    protected $fillable = [
        'email',
        'phone',
        'parent_id',
        'type',
        'name',
        'username',
        'loginBy',
        'address',
        'password',
        'otp_code',
        'otp_activated_at',
        'last_login',
        'agent',
        'host',
        'is_login',
        'is_active',
        'deleted_at',
        'created_at',
        'updated_at'
    ];

    protected $casts = [
        'is_login' => 'boolean',
        'is_active' => 'boolean'
    ];
    protected $dates = [
        'deleted_at',
        'created_at',
        'updated_at',
        'otp_activated_at',
        'last_login',
    ];


    protected $appends = [
        'birthday',
        'gender',
        'more'
    ];

    public function getMoreAttribute()
    {
        $metas = $this->accountsMetas()->get()->pluck('value', 'key')->toArray();
        return $metas;
    }

    public function getBirthdayAttribute()
    {
        return $this->meta('birthday') ?: null;
    }

    public function getGenderAttribute()
    {
        return $this->meta('gender') ?: null;
    }

    /**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
    public function accountsMetas()
    {
        return $this->hasMany('TomatoPHP\TomatoCrm\Models\AccountsMeta');
    }

    /**
* @param string $key
* @param string|null $value
* @return Model|string
*/
    public function meta(string $key, string|null $value=null): Model|string|null
    {
        if($value){
            return $this->accountsMetas()->updateOrCreate(['key' => $key], ['value' => $value]);
        }
        else {
            return $this->accountsMetas()->where('key', $key)->first()?->value;
        }
    }
    /**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
    public function activities()
    {
        return $this->hasMany('TomatoPHP\TomatoCrm\Models\Activity');
    }

    /**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
    public function comments()
    {
        return $this->hasMany('TomatoPHP\TomatoCrm\Models\Comment');
    }

    /**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
    public function locations()
    {
        return $this->hasMany('TomatoPHP\TomatoCrm\Models\Location');
    }

    public function groups(){
        return $this->belongsToMany(Group::class, 'account_groups', 'account_id', 'group_id');
    }
}

now your wallet is working you can start any transaction and check the customer’s balance.

Install Tomato CMS

In any functioning e-commerce setup, a well-optimized front end is indispensable. To enhance SEO performance and add essential content such as posts and pages, we’ll be incorporating our ‘tomato-cms’ package. Let’s proceed with the installation to seamlessly integrate this package and elevate our e-commerce platform.

composer require tomatophp/tomato-cms

now let’s install it

php artisan tomato-cms:install

Install Tomato Themes

To craft a dynamic frontend tailored as a theme, we’ll be utilizing the ‘tomato-themes’ package. This package simplifies the implementation of multi-themes for your project, employing a Hierarchical Model-View-Controller (HMVC) architecture. Let’s initiate the installation process for ‘tomato-themes’ to facilitate the seamless integration of diverse themes into your project.

composer require tomatophp/tomato-themes

now let’s install it

php artisan tomato-themes:install

now rebuild your assets

yarn & yarn build

make sure that you have Themes folder in your project root and add this line to your composer.json

 "autoload": {
"psr-4": {
"App\\": "app/",
"Database\\Factories\\": "database/factories/",
"Database\\Seeders\\": "database/seeders/",
"Themes\\": "Themes/"
}
},

now you need to reload the composer

composer dump-autoload

now inside your tailwind.config.js add these lines

content: [
    ...
    "./Themes/**/*.blade.php",
    "./Themes/**/**/*.blade.php",
],

now your theme is ready to upload or create a new theme.

Install Tomato E-Commerce

Now that we have our foundational elements in place, including a ready CRM, role management, and several essential packages, it’s time to bring it all together by installing the E-Commerce System. The seamless integration of these components promises a robust and feature-rich platform. Let’s proceed with the installation to witness the culmination of our efforts in building a comprehensive and efficient E-Commerce System.

composer require tomatophp/tomato-ecommerce

let’s install it

php artisan tomato-ecommerce:install

now everything i ready to install our e-commerce theme.

this package will install tomato-products, tomato-orders, tomato-offers, tomato-branches for you

to make everything work fine you need some actions, we need to install tomato-branches

php artisan tomato-branches:install

after that, you need to create just 1 Shipping Vendor from this endpoint /admin/shipping-vendors

now you need to change your site SEO data and your site Logo from this endpoint /admin/settings/seo upload your logos and change the Site Name.

Install E-commerce Theme

Having established a solid foundation, including CRM, role management, and various essential packages, we’re now ready to enhance the visual appeal of our E-Commerce and CMS platforms. Introducing our user-friendly theme with a simple yet stylish design – an ideal canvas for customization. Let’s embark on the installation process to seamlessly integrate this theme and provide users the flexibility to tailor it to their preferences.

inside your Themes Folder clone this repo

cd Themes

now clone our Theme.

git clone git@github.com:tomatophp/Ecommerce.git

now go to your dashboard /themes and you will get the new theme you can just activate it.

Please note that you must not have / route on your main routes/web.php because these routes can override the Theme Routes.

to fix style rebuild your assets

yarn & yarn build

now if you check your home page you will get something like this

you can select from the top dropdown any section and add it to your page.

and you can build a menu from /admin/menus using this endpoint

• Home /
• About /about
• Shop /shop
• Blog /blog
• Contact /contact
• Terms & Conditions /terms
• Privacy /privacy

you can create 2 menus main, footer the main will show up auto to your header and the footer to your footer.

change the middleware of Authenticate redirect to this route

<?php

namespace App\Http\Middleware;

use Illuminate\Auth\Middleware\Authenticate as Middleware;
use Illuminate\Http\Request;

class Authenticate extends Middleware
{
    /**
* Get the path the user should be redirected to when they are not authenticated.
*/
    protected function redirectTo(Request $request): ?string
    {
        return $request->expectsJson() ? null : route('accounts.login');
    }
}

now in your Account.php model add this trait

    use \TomatoPHP\TomatoEcommerce\Services\Traits\InteractsWithEcommerce;
    use \TomatoPHP\TomatoNotifications\Traits\InteractWithNotifications;
    use \TomatoPHP\TomatoOrders\Services\Traits\InteractsWithOrders;

With the successful installation of our comprehensive E-commerce system, you are now equipped to seamlessly manage your product catalog. Begin by adding new products and defining categories to tailor your offerings. Feel empowered to kickstart the order creation process, as your E-commerce system stands ready to facilitate smooth transactions and streamline your online business operations.

???? Thanks for using Tomato Plugins & TomatoPHP framework
???? Join the support server on Discord here
???? You can check docs here
⭐ Please give us a star on any repo if you like it TomatoPHP GitHub
???? Sponsor us here