In this tutorial we’re going to learn what are the steps for impoving the MySQL Query performance in Laravel.

1. What is Chunk function ?

The chunk method in Laravel is primarily designed to help with memory efficiency rather than directly improving MySQL query performance. It allows you to process large result sets in smaller “chunks” to avoid loading the entire dataset into memory at once. This can be beneficial when dealing with large datasets to prevent memory exhaustion.

Perfomance of using Chunk function ?

1. Memory Efficiency: When retrieving a large result set, fetching all records at once can consume a significant amount of memory. By using chunk, you process and handle a subset of records at a time, reducing the overall memory footprint.
2. Avoiding Timeout Issues: For long-running processes, especially in environments with script execution time limits, using chunk can help avoid script timeout issues. It ensures that the process is executed in smaller increments, preventing timeouts.

While chunk itself doesn’t directly optimize the underlying MySQL query, it indirectly contributes to better performance by addressing memory-related challenges. If you’re looking to optimize MySQL queries, you might want to focus on other aspects like proper indexing, query structure, and database design.

Example of Chunk function

public function get_organisation()
    {
         $getting_organisation = [];

        Organisation::latest()->chunkById(200, function ($organisations) use (&$getting_organisation) {
            foreach ($organisations as $organisation) {
                $getting_organisation[] = $organisation;
            }
        }, 'id');

        Log::info('coming all organisation in organisation page', ['data_count' => count($getting_organisation)]);
        return view('organization-page', compact('getting_organisation'));
    });

Output:-

Now open network and check the response timing.

Before of using Chunk function

After using of chunk function its reducing too much timing.

2. Use pluck Function

What is Pluck Function ?

The pluck() method in Laravel is a useful tool for retrieving specific values from a collection of data. It allows you to extract a single key from each item in the collection and return an array of those values. This can be particularly helpful when you only need to work with a subset of the data, or when you want to create a new array based on specific criteria.

Perfomance of using Pluck function in Laravel ?

1. Reduced Data Transfer: pluck retrieves only the specified column(s) from the database, reducing the amount of data transferred between the database and your application. This is particularly useful when dealing with large datasets.
2. Less Memory Usage: Since pluck only retrieves a single column from each row, it consumes less memory compared to fetching entire rows of data. This can be beneficial when working with limited memory resources.
3. Faster Processing: Smaller result sets, obtained through pluck, can lead to faster processing times, especially when you don’t need all the columns of each row. This can improve the overall speed of your application.
4. Optimized Queries: The generated SQL queries by pluck are optimized for fetching specific columns, and they are often more efficient than queries fetching all columns.

Example :-

public function get_organisation()
{
    $org_names = Organisation::latest()->pluck('org_name');

    Log::info('coming all organisation in organisation page', ['data_count' => count($org_names)]);

    return view('organization-page', compact('org_names'));
}

3. Better way to retrieve latest rows from a table

To retrieve the latest rows from a table in Laravel, you can use the latest method combined with get or other query builder methods. Here are a couple of examples:

The latest() scope provided by Laravel allows you to easily order the results by the specified column in descending order. To retrieve the latest row from a table, you can use the following syntax:

Example :-

public function getLatestRecord()
{
    $latests = User::latest()->get();

    dd($latests);
}

Example :-

Output

array:4 [

  0 => array:3 [

    "name" => "keval"

    "email" => "keval@gmail.com"

    "created_at" => "2019-12-26 00:00:00"

  ]

  1 => array:3 [

    "name" => "piyush"

    "email" => "piyush@gmail.com"

    "created_at" => "2019-12-25 00:00:00"

  ]

  2 => array:3 [

    "name" => "savan"

    "email" => "sava@gmail.com"

    "created_at" => "2019-12-09 00:00:00"

  ]

  3 => array:3 [

    "name" => "mehul"

    "email" => "mehul@gmail.com"

    "created_at" => "2019-12-01 00:00:00"

  ]

]

4. Table Indexing

Table indexing in Laravel is an essential aspect of database optimization. Indexing helps speed up query execution by providing a faster way to look up data. Laravel allows you to define indexes using migrations. Indexes are used to quickly retrieve data without having to search every row in a table every time a database table is accessed. Small database wont be noticed but once it grows, the query getting time consuming.

Without index, the process will be from top to bottom of the user data until it the email is found. Here is some comparison between with index and without it.

Without index — 750ms

With index — 2ms (400x faster)

In Laravel, the easy way is create a migration file to alter your desire table

Example :-

 public function up()
    {
        Schema::create('example', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->integer('age');
            $table->index(['name', 'age']);
            $table->timestamps();
        });
    }

Now migrate the table

php artisan migrate

By creating this migration, we can speed up our table. Keep in mind, however, this index will only be applied if we search the product using the whole title or the start of the title. 

5. Select only the columns you need

Selecting only the columns you need in Laravel is a good practice for optimizing performance and reducing unnecessary data transfer. You can use the select method on the query builder to specify the columns you want to retrieve.

Example :-

 $getting_value = DB::table('Organisation')->select('org_name')->get();

Replace 'your_table' with the actual name of your table and list the columns you need in the select method.

This way, only the specified columns will be retrieved from the database, improving performance and reducing the amount of data transferred.

Thanks for learning.