Tips to speed up database query in Laravel

Hafiq Iqmal

Feb 23·8 min read

Have you ever been wondering why your application slow even tough you already fix your application code?

Performance of the application is not depend on application itself. Application and database need to be tune together. For example, imagine 100 customers enter very big fancy restaurant (application) and there is only 2 waiter available (database). How can 2 waiter serve all the customer without keeping the customer in queue for long period? End up, the customer left the restaurant and never visit again.

Here is my 2 cents of tips to speed up database query based on my experience.

Table Indexing

Indexes is the most important part in Database. Indexing allow data retrieval efficiently faster. 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.

How to use? All you need is to identify which column you want to take advantage from. For example, you have 1.3 million rows of users and you want to grab an email.

select * from users where email = "connelly.prudence@example.net"

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

public function up()
{
    Schema::table('users', function(Blueprint $table) {
        $table->index('email');
    });
}

Or if you currently on fresh project just add index to column you want to

public function up()
{
    Schema::create('users', function(Blueprint $table) {
        ...
        $table->string('email')->index();
        ... 
    });
}

Note that,

1. Index is not only for single column. Sometime you need to search multiple column at once something like this $table->index([‘email’, ‘status’]);.
2. Speed of the query is depends on your Database CPU/Memory.

Eloquent ORM Eager Loading

Eloquent Object Relation Mapper (ORM) makes managing and working with these relationships so easy rather than manually join each table. In order to use Eloquent, there is some you need to consider to use it carefully which is Eager loading. Wrong implementation cause N+1 query. Here is where common developer do which cause N+1.

For example:

class User extends Model
{
   ...
   ...
   public function devices()
   {
       return $this->hasMany(Device::class);
   } 
}

Lets fetch users about 100 rows from the table and what happen

Mistake 1

$users = User::all();foreach($users as $user) {
    $total_devices_by_user = $user->devices->count();
}

The query runs once to get all the users and for each loop it query another query for devices for 100 times. 100 + 1 = N+1 problem. Imagine if you want to fetch thousand of data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.

Mistake 2

Manipulate relation table using append attribute.

class User extends Model
{
   ...
   ...
   protected $appends = ['device_count'];   public function devices()
   {
       return $this->hasMany(Device::class);
   } 
 
   public function getDeviceCountAttribute()
   {
       return $this->device->count();
   } 
}

And fetch the users

$users = User::all();

The query runs once to get all the users but the moment Laravel want to transform it into collection, because of appends attribute, the collection queried another 100 query. Its the same as Mistake 1 because it produce N+1 problem.

Solution

It can be solve be using with method in Eager loading in one go. For example

User::with('devices')->get();
User::with(['devices', 'posts'])->get();
User::with(['devices', 'posts.comments'])->get();

With this, instead of hitting database N+1, with will single query by joining those table and transform it into collection. So you can freely access the collection object without hitting the database. For more info about eager loading you can refer Laravel docs

Query Builder

Using eloquent is great because its have many cool features but when it come to the speed, it slightly slower than query builder because of ORM. Here is the example comparison between eloquent and query builder against 1.3 million rows

Eloquent

\App\Models\User::query()->get(); 
// 12.6 secondsUser::where('email', 'abc@gmail.com')->first(); 
// 1.3 seconds (Without index)User::where('email', 'abc@gmail.com')->first(); 
// 17 miliseconds (With index)

Query

\DB::table('users')->get(); 
// 7.4 seconds\DB::table('users')->where('email', 'abc@gmail.com')->first();
// 1.2 seconds (Without index)\DB::table('users')->where('email', 'abc@gmail.com')->first();
// 0.9 milisecond (With index)

Query most likely 2–3 times faster than eloquent. It doesn’t mean you can’t use Eloquent. I recommended to Eloquent but in certain cases, for simple approach like data retrieval without any complexity, you might need to use Query builder.

Paginate query

Paginate as many as you can for data listing (limit result per query) because its faster rather than retrieve all data at once. If you have 100 rows, it would be fine. But what if you what millions for rows. The best practice for data listing is to use pagination for best UI/UX.

In Laravel, there is 2 pagination — simplePaginate and paginate. Let’s test it against 1.3 million rows

User::query()->paginate(); // takes 950ms
User::query()->simplePaginate(); // takes 14ms

why paginate much slower (70% slower)? because it contains extra query to count all rows for total rows and other extra fields. Like I said, small data won’t be effect. If you have large dataset, you might consider simplePaginate

Always query what you really need

Here is also important to query only what you need. For example, let’s test on select query

User::query()->get(); // 13 secondsUser::query()->select('email')->get(); // 5.5 seconds\DB::table('users')->get(); // 7 seconds\DB::table('users')->select('email')->get(); // 1.2 seconds

Clearly select what column you need is much faster. There is several reasons why. You can refer here

Same goes to eager loading, don’t join table if you don’t need it. Some of developers most likely use $with variable in Model. My advise, just don’t. You need to manually choose which table you want to eager load instead of automatically bind it.

If you have a logic condition to check data existence in other table, don’t join it, use whereHas instead

Query Caching

Caching results of the queries which frequently accessed would increase the performance of an application. Cache basically store queries made for the first time and use it for the next request for some period of time or forever stored.

Let test on this query

User::query()->where('email', 'rempel.carolina@example.org')->get();

Without cache: 985ms (with index 17ms)

With cache: 0.4ms

Cache is faster than anything. Please avoid to use file or database as cache. File will produce high I/O and database will cause high connection — slow if high traffic. Recommended to use Redis or Memcached because it store in memory.

Here is some package you might to use to ease your implementation

Database Tuning

The purpose of tuning is where we need to find the best resources for the database. Best database need to be tune. It always not come by default. You need to check this 4 resources— CPU, RAM, Storage and Network. Lack of this will result poor performance on your application. It doesn’t mean if you have high spec of CPU and RAM, you can support high traffic with slow network.

My advise is always try and error. Do the load testing on your database so that you will know the capabilities of your database. Fine tune every possible configuration can be configure and see what happen.

Always use latest version

Its important to always use latest version of framework or database. Most update will give you performance upgrade also.

Why important?

1. Give you security updates
2. Keep your application running smoothly
3. Legacy system sometime give you problem in the future
4. Increase performance

For example in performance,

The benchmark between MySQL 5 and 8 which MySQL 8 give huge difference in the time it takes to process transactions and improvement in high workload.

For Laravel, there is also different in performance between L7 and L8 which based on my test is 2x faster than L7 and 4x faster than L6.

So, database and programming framework need to upgrade along (unless you are not using framework). Do benchmarking on multiple version and see how its going.

Separate Database from Application Server

Traditionally and most commonly found, entire environment puts on a single machine — where application, web server, database, and etc.. all together using same resources. Commonly used stack is LEMP (Linux, Nginx, MySQL, PHP).

Pros?

• Quick and easy for simple setup in little scalability.

Downside?

• Shared resources — lead to poor performance
• Difficulties to determine the source of poor performance
• If the machine shut down, everything gone

What related to this topic? Shared resource cause database unable to allocate process properly. If the application use 70% of CPU, database only have 30% or less just to process queries which lead to long query problem and timeout.

So, separating the database from the application server would be better. Why? By splitting database from application server, we can properly allocate service only for database and also increase the security by put it in private network.

Benefits?

• Fully optimized number of connection without been overloaded by other processes.
• Security purpose by isolated it from public network.
• Problem diagnosis and performance monitoring is easier.
• Maintenance and upgrade much easier when only focus on database without worrying other component

I would recommended to use cloud provider (AWS, Alibaba, DigitalOcean) when it comes to splitting environment because cloud provider have shared responsibility where you only need to care about your application and data. For maintaining the hardware and software is cloud provider responsibility.

Use Read-only database to retrieve data

If you have enough cost, splitting the database into read-only database would be good. Its provide flexibility, error tolerance, speed, and no locking occurred. Furthermore, read-only provides enhanced security for databases by protecting them from accidental or malicious updates.

So you will have 2 connection string. 1 — Read-Write database and 2 — Read-Only database. Frequent access data retrieve you might need to use readonly database. For less data retrieval and frequent update use the read-write database.

Conclusion

This is just several tips what i can help developers out there. Thanks for reading and don’t forget to share with you friends.

That’s all. Thanks for your time ~~