Some of the ways to optimize eloquent query on Laravel
Tags: laravel, eloquent, optimization, beginners
Published: 2020-10-01 11:01:34


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 <code>Orders</code> table and we want Orders of perticular Product. So we will query like below :
```php
$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 <code>EXPLAIN</code> 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 <code>Orders</code> table.

In laravel, We can add index on migration by using <code>$table->index()</code> method like below :
```php
Schema::table('orders', function (Blueprint $table) {
    $table->index('product_id');
});
```
This will create new index <code>orders_product_id_index</code>. Now if you run same query it will only take *~500 ms*. Run <code>EXPLAIN</code> 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 :
```php
$orders = Orders::where(['product_id' => $productId, 'status' => 1])->get();
```
We could add a compound index like this :
```php
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* :
```php
$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 :
```php
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.
```php
$orders = Customer::findMany($ids)->with('orders')->get();
```
The above query will generate SQL :
```php
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*

```php
$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 :
```php
$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 :*
```php
$products = Products::select('name','quantity','description')
                ->leftJoin('orders','orders.product_id','=','product_id');
                ->where('orders.status', 1)
                ->get();  
```
*Another query :*
```php
\DB::raw("GROUP_CONCAT(orders.id) AS orders")
```
In order to use both queries into one, we should use it like this :
```php
$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 :
```php
$orders = Orders::whereRaw('YEAR(order_date) = YEAR(CURRENT_DATE)');
```
I hope these techniques will decrease the execution time of eloquent queries.