Some of the ways to optimize eloquent query on Laravel

When we are working with large numbers of data & needs a frequent call to database queries then we must optimize database queries to make the application faster. I have listed some of the ways which can be useful to optimize a query.

Use Indexes

By using a database index, We can significantly improve the speed of retrieving data from a table because it's used to quickly find rows without having to scan the whole table every time. We can add an index for columns which we are frequently using in where clause like integer or boolean columns, datetime columns, etc.

Lets assume we have ~2 million records on Orders table and we want Orders of perticular Product. So we will query like below :

$orders = Orders::where('product_id', $productId)->get();

When the above query runs, it will scan all of the 2 million rows (as we didn't use any index yet) and return orders of that product. It will take around ~2 sec which is not good. Use EXPLAIN it will show 2 millions rows scanned and no key used.

Use EXPLAIN statement which provides information about how MySQL executes statements, which are available keys, which key is used to scan records and how many rows scanned etc..

Now add an index on Orders table.

In laravel, We can add index on migration by using $table->index() method like below :

Schema::table('orders', function (Blueprint $table) {
    $table->index('product_id');
});

This will create new index orders_product_id_index. Now if you run same query it will only take ~500 ms. Run EXPLAIN you will see difference in number of rows scanned, possible_keys and key.

We can also create Compound Index if we use multiple WHERE clauses in the query. which will include every field from WHERE clause. The order of the fields in a compound index can also have an impact. Generally, they should be ordered most common to the least common.

Say our query is :

$orders = Orders::where(['product_id' => $productId, 'status' => 1])->get();

We could add a compound index like this :

Schema::table('orders', function (Blueprint $table) {
    $table->index(['product_id', 'status']);
});

Prevent n+1 database queries

Consider example to understand what is n+1 query :

$customers = Customer::findMany($ids);
$orders = collect();

foreach ($customers as $customer) {
    $orders = $orders->merge($customer->orders);
}

When the above query executes SQL query like this gets generated :

SELECT * FROM customers WHERE id IN ($ids);
SELECT * FROM orders WHERE customer_id = $id; // execute in loop foreach customer

Here, the second query will be executed for each customer so if we have 500 customers it will be executed 500 times. So total 1 ( Query to get customers data) and 500 (Query to get orders for each customer) will be executed. So we can say n+1 query executes here for a single operation.

To prevent n+1 query, we can use eager loading technique. In which we can get everything in single query by using model relation.

$orders = Customer::findMany($ids)->with('orders')->get();

The above query will generate SQL :

SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id WHERE customers.id IN ($ids);

Eager Loading is best practice when we are working with relational tables.

Do not use asterisk wildcard (*) everywhere

$orders = Orders::all();

Many times while we are fetching data from the database we run queries like above. which will select all the data from the table but This is a bad practice you must avoid. Because it will perform extra queries, sometimes we don't use all of the columns and it will affect the performance of the application when the number of records increases on the database.

Best practice is to select fields which actually in use.

Use raw methods for a raw expression

Sometimes we need Raw Expression in a query which will be achieved by using raw methods like selectRaw, whereRaw , havingRaw , orderByRaw etc.

Which we are using it like :

$products = Products::select('name','quantity','description',\DB::raw("GROUP_CONCAT(orders.id) AS orders"))
                  ->leftJoin('orders','orders.product_id','=','product_id');
                  ->where('orders.status', 1)
                  ->get();    

Notice that, here it will run 2 queries into one.

First query :

$products = Products::select('name','quantity','description')
                ->leftJoin('orders','orders.product_id','=','product_id');
                ->where('orders.status', 1)
                ->get();  

Another query :

\DB::raw("GROUP_CONCAT(orders.id) AS orders")

In order to use both queries into one, we should use it like this :

$products = Products::selectRaw('name,quantity,description,GROUP_CONCAT(orders.id) as orders')
                ->leftJoin('orders','orders.product_id','=','product_id');
                ->where('orders.status', 1)
                ->get(); 

Same for where clause :

$orders = Orders::whereRaw('YEAR(order_date) = YEAR(CURRENT_DATE)');

I hope these techniques will decrease the execution time of eloquent queries.