multiple inner join mysql query taking more time to execute

[Senthilkumar]

Dear Team, 

I wrote a MySQL query with multiple inner joins using multiple tables. I am getting my required output with this query. But executing this query takes 6 to 7 seconds.

My query is 

SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM
				(SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM sbms.target as a
				inner join sbms.material as b on b.id = a.Category
				inner join sbms.equipment as c on c.eqipID = b.eqipID
				inner join sbms.plant as d on d.plantCode =  a.Branch 
                inner join sbms.branch as e on e.branchID =  d.branchID  where e.branchID='2' group by c.eqipID) TG

				LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as Billing from sbms.billing as a
				inner join sbms.division as b on b.divCode = a.division
				inner join sbms.equipment as c on c.eqipID = b.eqipID
				inner join sbms.distributionchannel as d on d.dcno = a.dchannel
				inner join sbms.plant as e on e.plantCode= a.sales_office
                inner join sbms.branch as f on f.branchID = e.branchID
				where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') between '2024-01' and '2024-03' and f.branchID='2'
				and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) Billing USING (Category)

				LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as PBilling from sbms.billing as a
				inner join sbms.division as b on b.divCode = a.division
				inner join sbms.equipment as c on c.eqipID = b.eqipID
				inner join sbms.distributionchannel as d on d.dcno = a.dchannel
				inner join sbms.plant as e on e.plantCode= a.sales_office
                inner join sbms.branch as f on f.branchID = e.branchID
				where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') = '2024-03'  and f.branchID='2'
				and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) PBilling USING (Category)

All my table dump and PHP files are attached to the link below

https://drive.google.com/file/d/1VLz-9EJoMEbxqbkykMT13iKaPLPu99u8/view?usp=sharing

 

Can anyone please suggest me to work this as fast.

[Barand]

0.36 seconds - Perhaps your battery needs recharging...

mysql> SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM
    ->     (SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM target as a
    ->     inner join material as b on b.id = a.Category
    ->     inner join equipment as c on c.eqipID = b.eqipID
    ->     inner join plant as d on d.plantCode =  a.Branch
    ->     inner join branch as e on e.branchID =  d.branchID  where e.branchID='2' group by c.eqipID) TG
    ->
    ->     LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as Billing from billing as a
    ->     inner join division as b on b.divCode = a.division
    ->     inner join equipment as c on c.eqipID = b.eqipID
    ->     inner join distributionchannel as d on d.dcno = a.dchannel
    ->     inner join plant as e on e.plantCode= a.sales_office
    ->     inner join branch as f on f.branchID = e.branchID
    ->     where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') between '2024-01' and '2024-03' and f.branchID='2'
    ->     and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) Billing USING (Category)
    ->
    ->     LEFT JOIN (select c.eqipName as Category, sum(a.gross_amount) as PBilling from billing as a
    ->     inner join division as b on b.divCode = a.division
    ->     inner join equipment as c on c.eqipID = b.eqipID
    ->     inner join distributionchannel as d on d.dcno = a.dchannel
    ->     inner join plant as e on e.plantCode= a.sales_office
    ->     inner join branch as f on f.branchID = e.branchID
    ->     where c.equipcatID = '1' and d.dcgroupid='1' and DATE_FORMAT(a.billing_date,'%Y-%m') = '2024-03'  and f.branchID='2'
    ->     and a.sales_doc_type !='ZL2W' and a.sales_doc_type !='ZS2' and a.sales_doc_type !='ZIPJ' group by c.eqipName) PBilling USING (Category);
+----------------+-----------+--------------------+----------+----------+----------+
| Category       | Budget    | Billing            | PTarget  | PBilling | CTarget  |
+----------------+-----------+--------------------+----------+----------+----------+
| Batching Plant | 210236000 | 17732014.850000016 | 14716520 |     NULL | 17519666 |
| Concrete Pump  | 125428000 | 18451593.089999996 |  8779960 |     NULL | 10452333 |
| TM             |  71957300 |  7340912.620000004 |  5037011 |     NULL |  5996442 |
| SLM            |  30373660 | 1678367.0800000003 |  2126156 |     NULL |  2531139 |
| Projects       |  95000000 |          306795.76 |  6650000 |     NULL |  7916667 |
+----------------+-----------+--------------------+----------+----------+----------+
5 rows in set (0.36 sec)

 

[Senthilkumar]

But when I am running it takes 7 sec.

Please suggest

[Barand]

The only difference I could see between your 2nd and 3rd subqueries was the 2nd summed months between 01 and 03 and the the 3rd only summed month 03, so I hve combined them and shaved 45% off the time

mysql> SELECT Category, Budget, Billing, PTarget,PBilling,CTarget FROM
    ->                 (SELECT c.eqipName as Category, sum(a.Budget) as Budget, sum(a.Mar) as PTarget, sum(a.Apr) as CTarget FROM target as a
    ->                 inner join material as b on b.id = a.Category
    ->                 inner join equipment as c on c.eqipID = b.eqipID
    ->                 inner join plant as d on d.plantCode =  a.Branch
    ->                 inner join branch as e on e.branchID =  d.branchID  where e.branchID='2' group by c.eqipID) TG
    ->      LEFT JOIN (
    ->                 SELECT c.eqipName AS Category
    ->                      , SUM(a.gross_amount) AS Billing
    ->                      , SUM(
    ->                         CASE DATE_FORMAT(a.billing_date,'%Y-%m')
    ->                             WHEN '2024-03' THEN a.gross_amount
    ->                             ELSE 0
    ->                             END
    ->                             ) AS PBilling
    ->                 FROM billing AS a
    ->                 INNER JOIN division AS b ON b.divCode = a.division
    ->                 INNER JOIN equipment AS c ON c.eqipID = b.eqipID
    ->                 INNER JOIN distributionchannel AS d ON d.dcno = a.dchannel
    ->                 INNER JOIN plant AS e ON e.plantCode= a.sales_office
    ->                 INNER JOIN branch AS f ON f.branchID = e.branchID
    ->                 WHERE c.equipcatID = '1'
    ->                       AND d.dcgroupid='1'
    ->                       AND DATE_FORMAT(a.billing_date,'%Y-%m') BETWEEN '2024-01' AND '2024-03'
    ->                       AND f.branchID='2'
    ->                       AND a.sales_doc_type NOT IN ('ZL2W', 'ZS2','ZIPJ')
    ->                 GROUP BY c.eqipName
    ->         ) Billing USING (Category);
+----------------+-----------+-------------+----------+----------+----------+
| Category       | Budget    | Billing     | PTarget  | PBilling | CTarget  |
+----------------+-----------+-------------+----------+----------+----------+
| Batching Plant | 210236000 | 17732014.85 | 14716520 |     0.00 | 17519666 |
| Concrete Pump  | 125428000 | 18451593.09 |  8779960 |     0.00 | 10452333 |
| TM             |  71957300 |  7340912.62 |  5037011 |     0.00 |  5996442 |
| SLM            |  30373660 |  1678367.08 |  2126156 |     0.00 |  2531139 |
| Projects       |  95000000 |   306795.76 |  6650000 |     0.00 |  7916667 |
+----------------+-----------+-------------+----------+----------+----------+
5 rows in set (0.20 sec)

Note that the typing of your columns is sloppy.

• You are joining ints to varchar(45)s
• INT(25) is somewhat optimistic (in you target spreadsheet table) - the max INT is 11 digits, not 25
• The amount column you are totalling is defined as VARCHAR(45)!!! - should be a numeric type such as DECIMAL(15,2).

[Senthilkumar]

Dear Mr.Barrand,

Thanks for your suggestion. The problem is joining ints to varchar(45). Now I corrected and it is working properly. 

 

[Barand]

Ensure all columns used in the joins are indexed. For example, adding an index on plant.branchid gave atime of 0.03 seconds.

+----------------+----------------+---------------+---------------+----------+---------------+
| Category       | Budget         | Billing       | PTarget       | PBilling | CTarget       |
+----------------+----------------+---------------+---------------+----------+---------------+
| Batching Plant | 210,236,000.00 | 17,732,014.85 | 14,716,520.00 | 0.00     | 17,519,666.00 |
| Concrete Pump  | 125,428,000.00 | 18,451,593.09 | 8,779,960.00  | 0.00     | 10,452,333.00 |
| TM             | 71,957,300.00  | 7,340,912.62  | 5,037,011.00  | 0.00     | 5,996,442.00  |
| SLM            | 30,373,660.00  | 1,678,367.08  | 2,126,156.00  | 0.00     | 2,531,139.00  |
| Projects       | 95,000,000.00  | 306,795.76    | 6,650,000.00  | 0.00     | 7,916,667.00  |
+----------------+----------------+---------------+---------------+----------+---------------+
5 rows in set (0.03 sec)