Laravel Database Query optimization

The Problem:

One of my Laravel projects suddenly grew in size, causing the number of requests to explode unexpectedly. The company I work for gained a lot of new clients all at once, but there wasn't a budget for proper testing and optimization, and time was also scarce. Despite my complaints, the executives didn't listen. As a result, the app didn't stop entirely but became very slow during peak hours, with some requests being rejected. After extensive investigation, it became clear that the database was the bottleneck. I'm using MySQL, and it appeared that too many queries were being executed simultaneously. Some of these queries were long and took a lot of time to execute. After removing most of the unnecessary queries, I needed to find a way to optimize the long-running queries.

Details:

One of the long-running queries was a specific query that calculated the available stock of products. This query ran frequently during the packaging operation, creating a bottleneck that stopped everything else.

The Solution:

Before working on the solution, I needed to determine how expensive these queries were. For that, I used the EXPLAIN ANALYZE command.

Note: ANALYZE will execute the query, so be careful when using it.

Example:

While I cannot reveal the company data, I will provide an illustrative example to explain the process.

This is part of the index function in the API/ProductController class:

/**  
 * Display a listing of the resource.
 */
public function index()  
{  
    if (request()-&gt;query('select')) {  
        $builder = Product::select('id', 'sku', 'name', 'variant', 'value');  
  
        if (request()-&gt;query('store_id')) {  
            $builder-&gt;where('store_id', request()-&gt;query('store_id'));  
        }  
        return $builder-&gt;get();  
    }
}

So far, so good. However, the products table has a variant_of_id column used to differentiate between products and product variants. All the variants of a product will have variant_of_id set to the ID of that product. In some cases, we need to return the variant product and the normal product but not products that have variants. To do that, we added a line to check for this condition.

Converting Laravel Code to SQL:

Let's start with the basic query:

SELECT id, name, sku, variant, variant_of_id  
FROM products 
WHERE store_id = 11;

Example result:

id	name	sku	variant	variant_of_id
27	Product Alpha	804325	null	null
49	Product Beta	674722	null	null
52	Product Gamma	994074	null	null
77	Product Delta	221396	null	null
98	Product Epsilon	921588	null	null
99	Product Zeta	974182	null	null
108	Product Eta	964955	null	null
170	Product Theta	488214	null	null
200	Product Iota	692533	null	null
201	Product Iota	6925331	Black-S	200
202	Product Iota	6925332	Black-M	200
203	Product Iota	6925333	Black-L	200
204	Product Iota	6925334	Black-XL	200

Running with EXPLAIN ANALYZE:

EXPLAIN ANALYZE 
SELECT id, name, sku, variant, variant_of_id  
FROM products 
WHERE store_id = 11;

Result:

-&gt; Index lookup on products using products_store_id_foreign (store_id=11)  (cost=5.44 rows=13) (actual time=0.0734..0.077 rows=13 loops=1)

It reads 13 rows, which is the number of products that the store has.

Adding the Condition:

To filter out products that have variants, we use the NOT EXISTS clause:

SELECT id, name, sku, variant, variant_of_id  
FROM products p  
WHERE store_id = 11  
  AND NOT EXISTS (SELECT 1  
                  FROM products v  
                  WHERE v.variant_of_id = p.id);

This query checks for the absence of rows where v.variant_of_id = p.id.

Running with EXPLAIN ANALYZE:

EXPLAIN ANALYZE  
SELECT id, name, sku, variant, variant_of_id  
FROM products p  
WHERE store_id = 11  
  AND NOT EXISTS (SELECT 1  
                  FROM products v  
                  WHERE v.variant_of_id = p.id);

Result:

-&gt; Nested loop antijoin  (cost=267 rows=2600) (actual time=0.0873..0.0929 rows=12 loops=1)
    -&gt; Index lookup on p using products_store_id_foreign (store_id=11)  (cost=5.44 rows=13) (actual time=0.0407..0.0429 rows=13 loops=1)
    -&gt; Single-row index lookup on  using  (variant_of_id=p.id)  (cost=43.8..43.8 rows=1) (actual time=0.0036..0.0036 rows=0.0769 loops=13)
        -&gt; Materialize with deduplication  (cost=43.8..43.8 rows=200) (actual time=0.0426..0.0426 rows=1 loops=1)
            -&gt; Filter: (v.variant_of_id is not null)  (cost=23.8 rows=200) (actual time=0.0348..0.0356 rows=4 loops=1)
                -&gt; Covering index scan on v using products_variant_of_id_foreign  (cost=23.8 rows=200) (actual time=0.0114..0.0297 rows=204 loops=1)

This result indicates that it reads more than 12 rows, looping through all 204 products to execute the condition.

Optimizing the Query:

By adding a store_id condition inside the EXISTS function, the query becomes more efficient:

EXPLAIN ANALYZE  
SELECT id, name, sku, variant, variant_of_id  
FROM products p  
WHERE store_id = 11  
  AND NOT EXISTS (SELECT 1  
                  FROM products v  
                  WHERE v.variant_of_id = p.id  
                    AND v.store_id = 11);

Result:

-&gt; Nested loop antijoin  (cost=23.6 rows=169) (actual time=0.0803..0.0889 rows=12 loops=1)  
    -&gt; Index lookup on p using products_store_id_foreign (store_id=11)  (cost=5.44 rows=13) (actual time=0.0483..0.0516 rows=13 loops=1)  
    -&gt; Single-row index lookup on  using  (variant_of_id=p.id)  (cost=6.84..6.84 rows=1) (actual time=0.0026..0.0026 rows=0.0769 loops=13)  
        -&gt; Materialize with deduplication  (cost=6.74..6.74 rows=13) (actual time=0.0279..0.0279 rows=1 loops=1)  
            -&gt; Filter: (v.variant_of_id is not null)  (cost=5.44 rows=13) (actual time=0.0182..0.0199 rows=4 loops=1)  
                -&gt; Index lookup on v using products_store_id_foreign (store_id=11)  (cost=5.44 rows=13) (actual time=0.0152..0.0184 rows=13 loops=1)

Confirming the Optimization:

To confirm, I used:

SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

Before running the query:

Variable_name	Value
Innodb_rows_read	286668

After running the query:

Variable_name	Value
Innodb_rows_read	286694

With a simple subtraction: 286694 - 286668 = 26, which is correct: 13 products plus 12 results plus 1 function.

Converting Back to Laravel ORM:

Given that the SQL query references the same table in different contexts, I used aliases. Laravel's Eloquent ORM doesn't support aliases directly, so I used the DB class for the second alias:

/**  
 * Display a listing of the resource. 
 */
public function index()  
{  
    if (request()-&gt;query('select')) {  
        $builder = Product::select('id', 'sku', 'name', 'variant', 'value');  
  


        $storeId = request()-&gt;query('store_id');  
        $noMain = request()-&gt;query('without_main');  
  
        if ($storeId) {  
            $builder-&gt;where('store_id', $storeId);  
  
            if ($noMain) {  
                $builder-&gt;whereNotExists(function ($query) use ($storeId) {  
                    $query-&gt;select(DB::raw(1))  
                        -&gt;from('products as v')  
                        -&gt;whereColumn('v.variant_of_id', 'products.id')  
                        -&gt;where('v.store_id', $storeId);  
                });  
            }  
        }  
        return $builder-&gt;get();  
    }
}

By optimizing this query, I was able to improve the performance of many long-running queries. As they say, understanding the problem is half of the solution.

Laravel Database Query optimization

The Problem:

Details:

The Solution:

Example:

Converting Laravel Code to SQL:

Running with EXPLAIN ANALYZE:

Adding the Condition:

Running with EXPLAIN ANALYZE:

Optimizing the Query:

Confirming the Optimization:

Converting Back to Laravel ORM:

Contact

Get in Touch

Phone

Email

Address