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

The performance of an application doesn’t just depend on the application code, it also depends on the database it uses. If the database is poorly designed or doesn’t have enough resources to handle the application’s traffic, the application can become slow or unresponsive.

For example, imagine a fancy restaurant (which is like an application) with 100 customers, but only two waiters (which are like the database). The two waiters won’t be able to serve all the customers quickly, and this can make the customers unhappy and frustrated. They may leave the restaurant and never come back.

Similarly, if the database isn’t optimized for the application’s traffic, it can cause delays in the application’s response time, which can frustrate users and cause them to abandon the application. Therefore, it’s important to make sure that both the application code and the database are optimized and work well together to provide a smooth user experience.

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

Table Indexing

Table indexing is a technique used in databases to optimize the performance of queries that search for specific data within a table. It involves creating special data structures, called indexes, that allow the database management system to quickly locate the rows that match a particular search criterion.

Indexes are created on one or more columns of a table, and they contain a sorted list of the values in those columns along with pointers to the corresponding rows in the table. When a query includes a search condition on one of the indexed columns, the database management system can use the index to quickly narrow down the search to a subset of the table’s rows that satisfy the condition, rather than having to scan the entire table. This can result in significant performance improvements for queries that search large tables or perform frequent searches.

It is important to note that creating indexes can also have downsides, such as increased storage requirements and slower performance for data modification operations (e.g., inserting, updating, or deleting rows), as the indexes must also be updated to reflect any changes to the table’s data. Therefore, the decision to create indexes should be based on careful analysis of the specific database and application requirements.

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

<?php
...
...
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

<?php
...
...
public function up()
{
    Schema::create('users', function(Blueprint $table) {
        ...
        $table->string('email')->index();
        // OR FOR MULTIPLE COLUMN
        $table->index(['email', 'status']);
        ... 
    });
}

Note that,

• Index is not only for single column. (Refer snippet above)
• Speed of the query is depends on your Database CPU/Memory. If you have potato database server, don’t expect too much

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.

<?php
...
...

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

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

Mistake 1: Common mistake used by Laravel developer

<?php
...

$users = User::all();
foreach($users as $user) {
    // There is where the part is mistake happend
    $total_devices_by_user = $user->devices->count(); 
}

Why? Because, the query actually runs once to get all the users BUT for each loop, its query another query for devices for 100 times. So, here is 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: Wrong Manipulation relation table using append attribute.

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


//
$users = User::all();
//

Why? Because, the query actually runs once to get all the users BUT the moment Laravel want to transform it into collection mapping from appends attribute, the collection queried another 100 query. Its the same as Mistake 1 because it produce N+1 problem.

Solution

Simple mistake can cause a lot of trouble. It can be solve be using with method in Eager loading in one go.

For example

<?php
...
...
//  
User::with('devices')->get();
// Multiple relationship
User::with(['devices', 'posts'])->get();
// Multiple deeper relation join
User::with(['devices', 'posts.comments'])->get();

////

With this, instead of hitting database N+1, with method will single query 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

<?php
\App\Models\User::query()->get(); 
// 12.6 seconds

User::where('email', 'abc@gmail.com')->first(); 
// 1.3 seconds (Without index)

User::where('email', 'abc@gmail.com')->first(); 
// 17 miliseconds (With index)

Query Builder

<?php

\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)

Based on the above comparison, Query builder are 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

<?php
...

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

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

...

?>

Why paginate much slower like 70% slower?

Let’s drill down code snippet from Illuminate\Database\Eloquent\Builder

<?php
....
....
/**
 * Paginate the given query.
 *
 * @param  int|null  $perPage
 * @param  array  $columns
 * @param  string  $pageName
 * @param  int|null  $page
 * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
 *
 * @throws \InvalidArgumentException
 */
public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
{
    $page = $page ?: Paginator::resolveCurrentPage($pageName);

    $perPage = $perPage ?: $this->model->getPerPage();

    $results = ($total = $this->toBase()->getCountForPagination())
                                ? $this->forPage($page, $perPage)->get($columns)
                                : $this->model->newCollection();

    return $this->paginator($results, $total, $perPage, $page, [
        'path' => Paginator::resolveCurrentPath(),
        'pageName' => $pageName,
    ]);
}

/**
 * Paginate the given query into a simple paginator.
 *
 * @param  int|null  $perPage
 * @param  array  $columns
 * @param  string  $pageName
 * @param  int|null  $page
 * @return \Illuminate\Contracts\Pagination\Paginator
 */
public function simplePaginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
{
    $page = $page ?: Paginator::resolveCurrentPage($pageName);

    $perPage = $perPage ?: $this->model->getPerPage();

    // Next we will set the limit and offset for this query so that when we get the
    // results we get the proper section of results. Then, we'll create the full
    // paginator instances for these results with the given page and per page.
    $this->skip(($page - 1) * $perPage)->take($perPage + 1);

    return $this->simplePaginator($this->get($columns), $perPage, $page, [
        'path' => Paginator::resolveCurrentPath(),
        'pageName' => $pageName,
    ]);
}

As you can see above snippet, at the line 21, the method paginate have extra counter to count all the rows in the tables while the simplePaginate not doing that. Like I said, small data won’t be effect. If you have large dataset, you might consider simplePaginate.

But, most cases, you actually need to use paginate to create table pagination with numbers right? Then, i would suggest you create a Macro method against Builder class which fix the count method.

Based on experience, most likely if you have a lot of data like 10 million+, it shouldn’t go more than 10 seconds. If YES, then you should check your database infrastructure OR most likely your code

Always query what you really need

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

<?php

// Using Eloquent
User::query()->get(); // 13 seconds

User::query()->select('email')->get(); // 5.5 seconds

// Using Query Builder
\DB::table('users')->get(); // 7 seconds

\DB::table('users')->select('email')->get(); // 1.2 seconds

Once again, comparison between Eloquent and Query Builder. Clearly Query builder much faster but the point is where to select what column you actually need is much-much faster. There is several reasons why. You can refer here for more info.

Same goes to the eager loading, don’t include relationship 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.

Don’t Count All

Yup, Count(*) very very slow if you have a lot of rows in the database. 1 Millions still ok, but if you tried 30 Millions row, its took several minutes to return the results.

It’s because of InnoDB is bad on counting rows without condition. My suggestion is that, instead of count all, count specific column that contain unique/primary key or foreign key. For example

Select count(id) from table;

For much better and faster result, add a where clause from indexed column

Select count(id) from table where indexed_column = 1;

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.

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.