Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts

    SQL query taking too long to execute

    Hi,

    I have below query taht takes around 10-15 min to execute even if the final output is 10records

    Select etd,(inv_model_amt+inv_option_amt) amt,company_name,remarks,prod_month, company_id, model_code, status_ag, order_number, chassis_front, chassis_number, engine_number, package, colour_code, upholstery_code, options, warehouse, lot from orders_current left outer join (select company_id comp_id,company_name from auth_company) ac on (orders_current.company_id=ac.comp_id) left outer join (select order_number number,remarks from orders_remarks) orem on (orders_current.order_number=orem.number) left outer join (SELECT warehouse,lot,GROUP_CONCAT(permit_type ORDER BY permit_type) AS pr,order_number num FROM permit_orders GROUP BY order_number) op on (orders_current.order_number=op.num) left outer join (select shipment_id,order_number so_order_number from shipment_orders) so on (orders_current.order_number=so.so_order_number) left outer join (select shipment_id,eta,etd from shipment ) s on (so.shipment_id=s.shipment_id) where orders_current.order_number IS NOT NULL and orders_current.company_id = '1' and status_ag = '95'and (eta is NULL or eta ='') AND YEAR(delivery_date)>2011 group by orders_current.order_number;

    Could someone help me to optimize thsi query.

    If i check the process list it takes too much time on "copying data to temp table" and "sending data"

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    What do you see when you ask MySQL to EXPLAIN that query?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts
    The output of EXPLAIN is as follows:
    Code:
    +----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+
    | id | select_type | table           | type  | possible_keys         | key     | key_len | ref   | rows  | Extra                                        |
    +----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+
    |  1 | PRIMARY     | orders_current  | ref   | PRIMARY,idex_4,idex_6 | idex_4  | 1       | const | 33198 | Using where; Using temporary; Using filesort |
    |  1 | PRIMARY     | <derived2>      | ALL   | NULL                  | NULL    | NULL    | NULL  |    19 |                                              |
    |  1 | PRIMARY     | <derived3>      | ALL   | NULL                  | NULL    | NULL    | NULL  |  7738 |                                              |
    |  1 | PRIMARY     | <derived4>      | ALL   | NULL                  | NULL    | NULL    | NULL  | 39729 |                                              |
    |  1 | PRIMARY     | <derived5>      | ALL   | NULL                  | NULL    | NULL    | NULL  | 49931 |                                              |
    |  1 | PRIMARY     | <derived6>      | ALL   | NULL                  | NULL    | NULL    | NULL  |  2247 | Using where                                  |
    |  6 | DERIVED     | shipment        | ALL   | NULL                  | NULL    | NULL    | NULL  |  2247 |                                              |
    |  5 | DERIVED     | shipment_orders | index | NULL                  | PRIMARY | 24      | NULL  | 49931 | Using index                                  |
    |  4 | DERIVED     | permit_orders   | ALL   | NULL                  | NULL    | NULL    | NULL  | 77637 | Using filesort                               |
    |  3 | DERIVED     | orders_remarks  | ALL   | NULL                  | NULL    | NULL    | NULL  |  7738 |                                              |
    |  2 | DERIVED     | auth_company    | ALL   | NULL                  | NULL    | NULL    | NULL  |    19 |                                              |
    +----+-------------+-----------------+-------+-----------------------+---------+---------+-------+-------+----------------------------------------------+

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, see all those lines where the type is "ALL"? That means MySQL is doing a full table scan in each case. And so of course it's slow.

    Time to look into what indexes could help your cause. Note that you don't even have any *possible* keys for all of those.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •