Learning from Experience — Dealing with Database in Laravel #1

photo credit Junar Eliang — round stair

I am starting a new series call #LearningFromExperience. In this series, I will share some of best practice, solution or any idea that I found during my day to day work.

This series also dedicated as a sharing area for me to learn from other developers, whether I did the right approach or maybe I can do some of other improvement in some area.

In this first post, I will share my recent experience to deal with the database in Laravel.

Don’t Forget to Index the Table

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly. — Postgres Documentation

Base on the statement from the PostgreSQL documentation, it mentions that index is like double side sword. In one side it can speed up your query, on the other it can harm your memory. So, use it wisely.

Before indexing the table, we need to make sure understand how will we use the table for the query. How complex is the query.

One of the feature that you can try to understand how big your query is by using “Explain” query in the PostgreSQL (same for MySQL). It will tell you about how much cost for your query.

For my case, We forget to index our table, and it cost us some pain in the *ss. When we implement the index, the query time drop significantly from 30s to only 2s. I believe we can further optimize this.

Check this interesting article from David Conlin — Postgres Indexing for Absolute Beginners

Use Eager Loading

Eager loading in eloquent, solving the issue to load multiple query to be executed, when we want to call the relationship of a model.

Let’s say we have this loop like this one.

use App\Models\Car;

$cars = Car::all();

foreach ($cars as $car) {
    echo $car->brand->name;
}

Since the relationship model will query when it is called. The loop at the top, will run the query for brand N times of the number of the Car model. If it has 20 cars, it will query 20 times for the brand.

To resolve this issue, we can use eager loading in laravel by using with method

use App\Models\Car;

$cars = Car::with('brand')->get();

foreach ($cars as $car) {
    echo $car->brand->name;
}

A small changes but bring quite big impact to your query process.

Check on Laravel Documentation about Eager Loading

Load the data in chunk for quite big rows

Recently we works to query thousands row of database query results. We just realized that it took up, a lot of time for the application to query to database.

Per guidance from Laravel documentation, we should consider to use chunk.

use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

For my case, I used this chunk during exporting the data into CSV, that makes the downloading process happen in the stream without waiting all rows finished downloading.

That’s it for this article. Laravel already provided some of guideline to make your application has better performance. Don’t forget to read their documentation, it will help a lot during development work.

Kindly drop your thoughts on the comment box below.