available stock calculation performance improvement

user2tyt 0
Viewed 50

I have a database structure like this:

Table: purchase_items:

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | bigint(110)   | NO   | PRI | NULL    | auto_increment |
| purchase_id         | int(11)       | NO   | MUL | NULL    |                |
| product_id          | int(11)       | NO   | MUL | NULL    |                |
| units               | varchar(100)  | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

Table: sales_items:

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| sales_id              | int(11)      | YES  | MUL | NULL    |                |
| product_id            | int(11)      | NO   | MUL | NULL    |                |
| purchase_item_id      | bigint(100)  | NO   | MUL | NULL    |                |
| qty                   | varchar(55)  | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

There is a sales_return_items, purchase_return_items and stock_adjustment table similar to sales_items

purchase_items table has more than 100k items

sales_items table has approx 1.5 million items

stock_adjustment table has more than 100k items

I am getting the inventory as following:

SELECT
    ped.*,
    COALESCE(si.qty, 0) AS sales_qty,
    COALESCE(sri.qty, 0) AS sales_return_qty,
    COALESCE(pri.qty, 0) AS purchase_return_qty,
    COALESCE(adj.qty, 0) AS adjustment_qty
FROM purchase_items ped
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS qty 
    FROM sales_items
    GROUP BY purchase_item_id
) si ON si.purchase_item_id = ped.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) AS qty
    FROM sales_return_item
    GROUP BY purchase_item_id
) sri ON sri.purchase_item_id = ped.id
LEFT JOIN  (
    SELECT purchase_item_id, SUM(qty) AS qty
    FROM purchase_return_items
    GROUP BY purchase_item_id
) pri ON pri.purchase_item_id = ped.id
LEFT JOIN (
    SELECT purchase_item_id, SUM(qty) as qty
    FROM adjustment_stock
    GROUP BY purchase_item_id
) adj ON adj.purchase_item_id = ped.id
GROUP BY ped.id;

This is working fine with small data. But when the data is increased it shows request timeout error. What is the solution for this?

This is explain select:

+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-----------------------------------------------------------------+
| id   | select_type     | table                 | type  | possible_keys | key      | key_len | ref                              | rows   | Extra                                                           |
+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-----------------------------------------------------------------+
|    1 | PRIMARY         | ped                   | ALL   | NULL                  | NULL             | NULL    | NULL                             | 111232 | Using temporary; Using filesort                 |
|    1 | PRIMARY         | <derived2>            | ref   | key0                  | key0             | 9       | ped.id                           |      2 |                                                 |
|    1 | PRIMARY         | <derived3>            | ref   | key0                  | key0             | 9       | ped.id                           |     10 |                                                 |
|    1 | PRIMARY         | <derived4>            | ref   | key0                  | key0             | 9       | ped.id                           |      5 |                                                 |
|    1 | PRIMARY         | <derived5>            | ALL   | NULL                  | NULL             | NULL    | NULL                             | 161674 | Using where; Using join buffer (flat, BNL join) |
|    5 | DERIVED         | adjustment_stock      | index | NULL                  | purchase_item_id | 768     | NULL                             | 161674 |                                                 |
|    4 | DERIVED         | purchase_return_items | ALL   | purchase_item_id      | NULL             | NULL    | NULL                             |     50 | Using temporary; Using filesort                 |
|    3 | DERIVED         | sales_return_item     | ALL   | purchase_item_id      | NULL             | NULL    | NULL                             |  32185 | Using temporary; Using filesort                 |
|    2 | LATERAL DERIVED | sales_items           | ref   | purchase_item_id      | purchase_item_id | 8       | ped.id                           |      6 |                                                 |
+------+-----------------+-----------------------+-------+---------------+----------+---------+----------------------------------+--------+-----------------------------------------------------------------+


Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 - Ubuntu 20.04
Protocol version: 10
Database client version: libmysql - mysqlnd 7.4.3
PHP extension: mysqli Documentation curl Documentation mbstring
PHP version: 7.4.3
mysql
1 Answers

Here is one option, which however will require a totally different way of storing your data and also a new train of thought. You may create summary tables for each table which appears in an aggregation subquery. For example, for the sales_items table, create the following table:

sales_items_summary (batch_no, qty)

Every time you insert a new record into sales_items, you can update the above table for the appropriate batch number record and increment the quantity. This way, you maintain the sum of quantities per batch, as you go along. Now when you need to report, you may join to sales_items_summary on the batch_no. The sum of quantities are available without a costly aggregation.

Tim Biegeleisen0
Related