Tips to Speed up your database queries in Laravel

Here is some tips based on my experience to speed up database queries.

Table Indexing

Indexes are important part in database. Indexes are used to quickly retrieve data without having to search every row in a database table is accessed. The users cannot see the indexes, they are just used to speed up searches/queries.

How to use? For example, you have thousands of rows of users and you want to get an email.

Select * from users where email = “me@gmail.com”

Without index, this process will be from top to bottom until it the email is found.

Using with index, in Laravel, the easy way is to create a migration file.

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->index(‘email’);
      …
    });
}

Index is not only for single column. You can use multiple column like this  $table->index([‘email’, ‘ phone_no’]); Let’s us look at a query that could use two different indexes on the table based on the WHERE clause restrictions. Multi column indexes are called compound indexes.

Eloquent Eager Loading

At its core Eager Loading, is telling Eloquent that you want to grab a model with specific relationships that way the framework produces a more performant query to grab all the data you will need. By eager loading, you can take many queries down to just one or two. Wrong implementation cause N+1. Here is where common developer do which cause N+1.

For example:

Class Post extends Model
{
  public function author()
  {
    return $this->belongsTo(Author::class);
  }
}

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

Mistake 1

$post = Post::all();
foreach($posts as $post) 
{
  $total_user = $post->author;
}

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

Solution

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

$post = App\Post::with(‘author’)->get();

If you had multiple related associations, you can eager load them with an array:

$post = App\Post::with([‘author’, ‘comments’])->get();
$post =App\Post::with(‘author.profile’)->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.

By Ami