Mysql Join Query optimisation

Question

Hello i am trying to run this select statement using this query and it is taking over 2 hours to run. I have set up all the index's to be correct. But it still takes forever is there something i am missing or a more efficient way of joining tables together that will speed this query up?

I have indexes set up for all items being joined together and they are the same length and data type.

SELECT 
    p.sap_article_id, 
    p.numeric_line_code, 
    p.uag_linecode,  
    p.uag_partnum,   
    p.part_description, 
    p.jobber_price, 
    p.jobber_core, 
    p.discount1,  
    p.discount2, 
    p.uom, 
    p.product_category, 
    w.as400_warehouse,   
    w.atp_qty, 
    p.updated,  
    t.regular_discount     
FROM part p 
        LEFT JOIN tabjbmaw t ON t.accountnum        = '73050'
                            AND p.numeric_line_code = t.numeric_line_code 
                            AND p.sub_code          = t.sub_code    
        JOIN warehouse w ON w.sap_article_id = p.sap_article_id;

+----+-------------+-----------+------+--------------------------------------------------+-----------------------+---------+----------------------------------+--------+-------------+
| id | select_type | table     | type | possible_keys                                    | key                   | key_len | ref                              | rows   | Extra       |
+----+-------------+-----------+------+--------------------------------------------------+-----------------------+---------+----------------------------------+--------+-------------+
|  1 | SIMPLE      | part      | ALL  | PRIMARY,sap_article,part_sap_article_id_fk       | NULL                  | NULL    | NULL                             | 389309 |             |
|  1 | SIMPLE      | warehouse | ref  | article                                          | article               | 130     | inventory.part.sap_article_id    |      5 | Using where |
|  1 | SIMPLE      | tabjbmaw  | ref  | numeric_line_code_idx,subcode_idx,accountnum_idx | numeric_line_code_idx | 5       | inventory.part.numeric_line_code |     19 |             |
+----+-------------+-----------+------+--------------------------------------------------+-----------------------+---------+----------------------------------+--------+-------------+

Thank you for your help

+-----------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field                       | Type         | Null | Key | Default             | Extra                       |
+-----------------------------+--------------+------+-----+---------------------+-----------------------------+
| sap_article_id              | varchar(24)  | NO   | PRI |                     |                             |
| sap_brand_id                | varchar(20)  | NO   |     | NULL                |                             |
| uag_partnum                 | varchar(20)  | NO   | MUL | NULL                |                             |
| uag_linecode                | varchar(5)   | NO   | MUL | NULL                |                             |
| cag_partnum                 | varchar(20)  | NO   | MUL | NULL                |                             |
| cag_linecode                | varchar(5)   | NO   |     | NULL                |                             |
| product_category_legacy     | varchar(20)  | NO   |     | NULL                |                             |
| part_description            | varchar(128) | NO   |     | NULL                |                             |
| abc_indicator               | varchar(8)   | NO   |     | NULL                |                             |
| pack_code                   | varchar(8)   | NO   |     | NULL                |                             |
| case_qty                    | int(11)      | NO   |     | NULL                |                             |
| per_car_qty                 | int(11)      | NO   |     | NULL                |                             |
| uom                         | varchar(6)   | NO   |     | NULL                |                             |
| upc_code                    | varchar(128) | NO   |     | NULL                |                             |
| jobber_price                | float(14,4)  | YES  |     | NULL                |                             |
| jobber_core                 | float(14,4)  | YES  |     | NULL                |                             |
| date_last_price_change      | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| weight                      | float(14,4)  | YES  |     | NULL                |                             |
| weight_unit                 | varchar(6)   | NO   |     | NULL                |                             |
| dimension_type              | varchar(6)   | NO   |     | NULL                |                             |
| length                      | float(14,4)  | YES  |     | NULL                |                             |
| width                       | float(14,4)  | YES  |     | NULL                |                             |
| height                      | float(14,4)  | YES  |     | NULL                |                             |
| updated                     | tinyint(1)   | NO   |     | 0                   |                             |
| superseded_sap_article_id   | varchar(24)  | YES  |     | NULL                |                             |
| last_updated                | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| hour_updated                | int(11)      | YES  |     | NULL                |                             |
| discount1                   | float        | YES  |     | NULL                |                             |
| discount2                   | float        | YES  |     | NULL                |                             |
| product_category            | varchar(3)   | YES  |     | NULL                |                             |
| superseded_part_number      | varchar(20)  | YES  |     | NULL                |                             |
| sub_code                    | varchar(3)   | YES  | MUL | NULL                |                             |
| date_effective_price_change | date         | YES  |     | NULL                |                             |
| numeric_line_code           | varchar(3)   | YES  | MUL | NULL                |                             |
| list                        | float        | YES  |     | NULL                |                             |
+-----------------------------+--------------+------+-----+---------------------+-----------------------------+

What kind of JOIN do you want on the warehouse table? Have you tried moving '73050' = tabjbmaw.accountnum to a WHERE clause? — Melanie
– Melanie, Commented Mar 19, 2013 at 19:24
No, that would be a terrible mistake to remove that statement from the LEFT JOIN part. First of all the optimizer works the same in JOIN and WHERE clauses, and second a filter inserted to a WHERE clause makes the associated table INNER joined, which is incorrect in this case because he wants an OUTER join. — Sebas
– Sebas, Commented Mar 19, 2013 at 19:28
we need empty values back in the discount is not there so moving it to the end doesn't help us. it needs to stay in there — user1863457
– user1863457, Commented Mar 19, 2013 at 19:29
@ITroubs Its a left outer join not an inner join, moving the check to the where would yield a different result. — Magnus
– Magnus, Commented Mar 19, 2013 at 19:31

Marlin Pierce · Accepted Answer · 2013-03-19 19:38:36Z

1

I have indexes set up for all items being joined together

Yes, but I am guessing from the names of the indexes that each index only has one field.

Let's look at a few columns in the describe.

| table     | possible_keys                                    | key                   
+-----------+--------------------------------------------------+----------------
| part      | PRIMARY,sap_article,part_sap_article_id_fk       | NULL                  
| warehouse | article                                          | article               
| tabjbmaw  | numeric_line_code_idx,subcode_idx,accountnum_idx | numeric_line_code_idx

It can use an index for numeric_line_code, subcode, and accountnum, but there are only three indexes each with one of the fields, and no index which has all the fields. You are making the optimizer choose one of the one field indexes, instead of providing one index it can use for all three fields.

Add an index on table tabjbmaw with the three fields numeric_line_code, subcode, and accountnum.

answered Mar 19, 2013 at 19:38

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marlin Pierce Over a year ago

how fast was it after you added the new index?

Populus · Accepted Answer · 2013-03-19 19:38:09Z

0

Extending @Sebas answer, you should select tabjbmaw first:

SELECT 
    p.sap_article_id, 
    p.numeric_line_code, 
    p.uag_linecode,  
    p.uag_partnum,   
    p.part_description, 
    p.jobber_price, 
    p.jobber_core, 
    p.discount1,  
    p.discount2, 
    p.uom, 
    p.product_category, 
    w.as400_warehouse,   
    w.atp_qty, 
    p.updated,  
    t.regular_discount     
FROM tabjbmaw t
        LEFT JOIN parts p ON p.numeric_line_code = t.numeric_line_code 
                             AND p.sub_code       = t.sub_code    
        JOIN warehouse w ON w.sap_article_id = p.sap_article_id
WHERE t.accountnum = '73050'
;

answered Mar 19, 2013 at 19:38

Populus

7,7003 gold badges42 silver badges56 bronze badges

3 Comments

Sebas Over a year ago

if so that would be a very bad functional mistake from him :D But I agree with you it seems more logical

Marlin Pierce Over a year ago

This will, of course, give different results, since it will miss any parts which do not match the join with tabjbmaw. Also, it includes tabjbmaw records which do not match, when that was not part of the result before. Maybe changing LEFT JOIN to INNER JOIN would remove the missing parts, but not introduce missing tabjbmaw records.

G-Nugget Over a year ago

This will not produce the same results as the original query. The LEFT table here is tabjbmaw, not parts as in the original query. Also, I don't think this would make much of a difference; it would still have to do a full table scan on parts.

ITroubs · Accepted Answer · 2013-03-19 19:47:11Z

0

You could try to put your Left Join into the SELECT part as a Subselect. That 'may' speed things up a little.

Like this:

SELECT 
    p.sap_article_id, 
    p.numeric_line_code, 
    p.uag_linecode,  
    p.uag_partnum,   
    p.part_description, 
    p.jobber_price, 
    p.jobber_core, 
    p.discount1,  
    p.discount2, 
    p.uom, 
    p.product_category, 
    w.as400_warehouse,   
    w.atp_qty, 
    p.updated,  
    (SELECT t.regular_discount FROM tabjbmaw t WHERE t.accountnum = '73050' AND p.numeric_line_code = t.numeric_line_code AND p.sub_code = t.sub_code LIMIT 1)    
FROM 
    part p 
    JOIN warehouse w ON w.sap_article_id = p.sap_article_id;

answered Mar 19, 2013 at 19:47

ITroubs

11.2k4 gold badges30 silver badges25 bronze badges

1 Comment

G-Nugget Over a year ago

This only allows one result from tabjbmaw, but the original query allows for multiple. Also, JOINs are almost always faster than subqueries.

Collectives™ on Stack Overflow

Mysql Join Query optimisation

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related