Mysql join query taking long time to exceute the output

[Senthilkumar]

Dear Team,

I am using the below join query to get output 

select  sum(a.gross_amount) AS Total, b.DistributionChannelDesp AS Department, c.branchName AS Branch, d.grpName AS DepartmentGroup, e.equiSubName AS Equipment, g.mgName AS Material from sbms.billing AS a
INNER JOIN sbms.department_code AS b ON b.DChannel = a.dchannel
INNER JOIN sbms.branch AS c ON c.branchcode = a.sales_office 
INNER JOIN sbms.dept_group AS d ON d.grpID = b.grpID
INNER JOIN sbms.equipmentsubcategory AS e ON e.eqipSubCode = a.division 
INNER JOIN sbms.materialsubgroup AS f ON f.mgsubNumber = a.material 
INNER JOIN sbms.materialgroup AS g ON g.mgID = f.mgID 
WHERE a.sales_office='801' group by b.DistributionChannelDesp, d.grpID, e.equiSubName, g.mgID

When I am running this query, it takes about 300 seconds. The main table billing has more than 300,000 rows, and materialsubgroup has more than 7000 rows.

I attached my dumps & sample output to the below link.

https://drive.google.com/drive/folders/1IH3FobEkPqnsAwLEPerJJEh-jp7vR4Di?usp=sharing

Please help me where i am making the mistake

[Barand]

1. Put indexes on the columns that you are joining on. This speeds things up considerably as it doesn't have to read the entire joined table to find a match. As you can see...
2. The fields you you are joining on should be of the same type. You are joining on columns defined as int in one table and varchar in the other.

[Senthilkumar]

Dear MR.Barand,

I changed the column type same at both the table. Then also the query is taking long time to finesh it.

[Senthilkumar]

how to put the index on the columns

[Barand]

Easiest is with mysql workbench.

• Expand the table and columns
• Right-click coumn name and select "Create index"
  
• Click "Create"

[Senthilkumar]

I have created the index for columns dchannel, sales_office, division, and material on table billing. Then also same 

[Barand]

And the other tables?

[Senthilkumar]

I created the index for all the columns used in the join query. Now the qury is completing in 30 to 35 seconds.

Can you please confirm if this is okay?

[Barand]

Should be faster than that

mysql> SELECT  sum(a.gross_amount) AS Total, b.DistributionChannelDesp AS Department, c.branchName AS Branch,
    ->         d.grpName AS DepartmentGroup, e.equiSubName AS Equipment, g.mgName AS Material
    -> FROM sbms.billing AS a
    -> INNER JOIN sbms.department_code AS b ON b.DChannel = a.dchannel
    -> INNER JOIN sbms.branch AS c ON c.branchcode = a.sales_office
    -> INNER JOIN sbms.dept_group AS d ON d.grpID = b.grpID
    -> INNER JOIN sbms.equipmentsubcategory AS e ON e.eqipSubCode = a.division
    -> INNER JOIN sbms.materialsubgroup AS f ON f.mgsubNumber = a.material
    -> INNER JOIN sbms.materialgroup AS g ON g.mgID = f.mgID
    -> WHERE a.sales_office='801'
    -> GROUP BY b.DistributionChannelDesp, d.grpID, e.equiSubName, g.mgID;

+--------------------+---------------------+---------+------------------+----------------------+------------------------------------------+
| Total              | Department          | Branch  | DepartmentGroup  | Equipment            | Material                                 |
+--------------------+---------------------+---------+------------------+----------------------+------------------------------------------+
|           16368308 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | OMC Charges                              |
|            2549368 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Service Chgs-Repair work @ customer site |
|            4217060 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Service Charges for Plant Installation   |
|            3567304 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Service Charges for Calibration          |
|            1038001 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Machinery Software Updation Charges      |
|             968000 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Annual Maintenance Contract Charges      |
|             400000 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Equipment Hiring Charges                 |
|           201500.3 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Schwing Cloud Solns - Batching Plant     |
|              60000 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | AUTOMATION SERVICES                      |
|           26502.93 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Wear Parts                               |
|             771.47 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Consumables                              |
|              78795 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Electrical  Parts                        |
|           45743.13 | Cust. Service       | CHENNAI | Domestic-Parts   | Batch plants         | Other Parts                              |
|             739820 | Cust. Service       | CHENNAI | Domestic-Parts   | Excavator            | OMC Charges                              |
... etc

495 rows in set (1.55 sec)

 

[Barand]

I hope I am wrong on this, but my theory is that if we total all the values in your query output "Total" column to get an overall total then that should equal the total of all billing.gross_amount values for sales_office 801.

mysql> SELECT format(sum(x.Total),0) as grandTotal
    -> FROM (
    ->         SELECT  sum(a.gross_amount) AS Total, b.DistributionChannelDesp AS Department, c.branchName AS Branch,
    ->                 d.grpName AS DepartmentGroup, e.equiSubName AS Equipment, g.mgName AS Material
    ->         FROM sbms.billing AS a
    ->         INNER JOIN sbms.department_code AS b ON b.DChannel = a.dchannel
    ->         INNER JOIN sbms.branch AS c ON c.branchcode = a.sales_office
    ->         INNER JOIN sbms.dept_group AS d ON d.grpID = b.grpID
    ->         INNER JOIN sbms.equipmentsubcategory AS e ON e.eqipSubCode = a.division
    ->         INNER JOIN sbms.materialsubgroup AS f ON f.mgsubNumber = a.material
    ->         INNER JOIN sbms.materialgroup AS g ON g.mgID = f.mgID
    ->         WHERE a.sales_office='801'
    ->         GROUP BY b.DistributionChannelDesp, d.grpID, e.equiSubName, g.mgID
    -> ) x ;
+---------------+
| grandTotal    |
+---------------+
| 1,998,441,259 |
+---------------+
1 row in set (1.47 sec)

mysql> SELECT format(sum(a.gross_amount),0) AS Total_801
    -> FROM billing a
    -> WHERE sales_office='801';
+---------------+
| Total_801     |
+---------------+
| 6,195,206,276 |
+---------------+
1 row in set (0.74 sec)

As you can see there is a minor discrepancy of around 4 billion. (4,196,765,017 to be precise).