![Image](https://slimani.dev/storage/Posts/1/image/conversions/street-big-city-from-bird-s-eye-view-(1)-header.jpg)
Laravel Database Query optimization
- Category Laravel Databases
- Date 29/06/2024
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()->query('select')) {
$builder = Product::select('id', 'sku', 'name', 'variant', 'value');
if (request()->query('store_id')) {
$builder->where('store_id', request()->query('store_id'));
}
return $builder->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:
-> 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:
-> Nested loop antijoin (cost=267 rows=2600) (actual time=0.0873..0.0929 rows=12 loops=1)
-> 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)
-> 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)
-> Materialize with deduplication (cost=43.8..43.8 rows=200) (actual time=0.0426..0.0426 rows=1 loops=1)
-> Filter: (v.variant_of_id is not null) (cost=23.8 rows=200) (actual time=0.0348..0.0356 rows=4 loops=1)
-> 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:
-> Nested loop antijoin (cost=23.6 rows=169) (actual time=0.0803..0.0889 rows=12 loops=1)
-> 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)
-> 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)
-> Materialize with deduplication (cost=6.74..6.74 rows=13) (actual time=0.0279..0.0279 rows=1 loops=1)
-> Filter: (v.variant_of_id is not null) (cost=5.44 rows=13) (actual time=0.0182..0.0199 rows=4 loops=1)
-> 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()->query('select')) {
$builder = Product::select('id', 'sku', 'name', 'variant', 'value');
$storeId = request()->query('store_id');
$noMain = request()->query('without_main');
if ($storeId) {
$builder->where('store_id', $storeId);
if ($noMain) {
$builder->whereNotExists(function ($query) use ($storeId) {
$query->select(DB::raw(1))
->from('products as v')
->whereColumn('v.variant_of_id', 'products.id')
->where('v.store_id', $storeId);
});
}
}
return $builder->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.
Tags :
- laravel ;
- database ;
- mysql ;
- optmization