GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimize query with indexes all over the place

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize query with indexes all over the place

    I need some help optimizing the following query
    SELECT sql_calc_found_rows ob.order_batch_idnum,
    oi.order_idnum,
    ost.order_stock_type_idnum,
    oag.order_activity_group_idnum,
    ob.request_contact_idnum,
    oag.order_group_idnum,
    oi.attention,
    oi.customer_name,
    oi.address_city,
    oi.state_code,
    DATE_FORMAT(oi.ship_by_date,'%m/%d/%Y') AS ship_by_date,
    CONCAT_WS(' ',rcon.name_first,rcon.name_last) AS requested_by,
    CONCAT_WS(' ',mcon.name_first,mcon.name_last) AS placed_by,
    IF(ost.print_pick_ticket_date > '0000-00-00 00:00:00',
    DATE_FORMAT(ost.print_pick_ticket_date,'%m/%d/%Y'),
    'none') AS print_pick_ticket_date,
    DATE_FORMAT(ob.order_date,'%m/%d/%Y') AS order_date,
    DATE_FORMAT(t.ship_date,'%m/%d/%Y') AS ship_date,
    DATE_FORMAT(ost.invoice_date,'%m/%d/%Y') AS invoice_date,
    IF(ost.deliver_date,DATE_FORMAT(ost.deliver_date,' %m/%d/%Y') ,
    IF(oag.deliver_date,DATE_FORMAT(oag.deliver_date,' %m/%d/%Y') ,
    '')) AS deliver_date,
    sv.status_name,
    ost.current_status,
    oa.master_customer_idnum,
    pst.product_stock_type,
    pl.plant_name,
    (SELECT SUM(oa_count.quantity_requested)
    FROM order_activity oa_count
    WHERE oa_count.order_stock_type_idnum = oa.order_stock_type_idnum
    GROUP BY oa_count.order_stock_type_idnum) AS qty_total,
    CONCAT(IF(NOT ISNULL(oi.address_street1),CONCAT(oi.address_stree t1,' '),
    ''),IF(NOT ISNULL(oi.address_street2),CONCAT(oi.address_stree t2,'
    '),
    ''),IF(NOT ISNULL(oi.address_city),CONCAT('',oi.address_city) ,
    ''),IF(NOT ISNULL(oi.country_division_idnum)
    AND oi.country_division_idnum != 0,CONCAT(', ',cd.division_code),
    ''),IF(NOT ISNULL(oi.address_zip5),CONCAT(' ',oi.address_zip5),
    ''),IF(NOT ISNULL(oi.country_idnum)
    AND oi.country_idnum != 0,CONCAT('
    ',c.country),
    '')) AS ship_address,
    IF(MAX(os.time_stamp) > '0',DATE_FORMAT(MAX(os.time_stamp),'%m/%d/%Y'),
    'none') AS approved_date,
    pur.purpose_name,
    CONCAT(pro.promotion_name,', ',pta.promotion_activity_name) AS promotion_name,
    cus.customer_name AS client_name,
    oi.country_idnum,
    oi.address_zip5,
    sp.carrier_service_idnum,
    IF(ost.deliver_date,ost.deliver_date,IF(oag.delive r_date,oag .deliver_date,'')) AS need_date_timestamp,
    lb.label_name,
    dist.customer_name AS dist_of_record,
    oag.order_comments,
    t.order_id,
    sp.ship_priority_idnum,
    (SELECT COUNT(t_count.tracking_idnum) AS tracking_count
    FROM shipping.tracking t_count
    WHERE t_count.order_stock_type_idnum = ost.order_stock_type_idnum
    AND t_count.void != 1
    GROUP BY t_count.order_stock_type_idnum) AS tracking_summary,
    oi.address_zip5,
    oi.country_idnum,
    sp.ship_priority_idnum,
    sp.carrier_service_idnum,
    IF(ost.deliver_date,ost.deliver_date,IF(oag.delive r_date,oag .deliver_date,'')) AS need_date_timestamp,
    pst.food_stock_type_idnum,
    ot.operation_type,
    cus_usr.customer_idnum
    FROM order_stock_type ost
    LEFT JOIN order_activity oa
    ON (oa.order_stock_type_idnum = ost.order_stock_type_idnum)
    AND (oa.kit_master_idnum = 0)
    LEFT JOIN order_info oi
    ON oi.order_idnum = ost.order_idnum
    LEFT JOIN order_batch ob
    ON ob.order_batch_idnum = ost.order_batch_idnum
    LEFT JOIN order_activity_group oag
    ON oag.order_activity_group_idnum = ost.order_activity_group_idnum
    LEFT JOIN order_status os
    ON oa.order_activity_idnum = os.order_activity_idnum

    LEFT JOIN product_stock_type pst
    ON ost.product_stock_type_idnum = pst.product_stock_type_idnum
    LEFT JOIN status_value sv
    ON sv.status_idnum = ost.current_status
    LEFT JOIN shipping.tracking t
    ON t.order_stock_type_idnum = ost.order_stock_type_idnum
    LEFT JOIN product p
    ON p.product_idnum = oa.product_idnum
    LEFT JOIN contact rcon
    ON rcon.contact_idnum = ob.request_contact_idnum
    LEFT JOIN contact mcon
    ON mcon.contact_idnum = ob.master_contact_idnum
    LEFT JOIN general.country_division cd
    ON cd.country_division_idnum = oi.country_division_idnum
    LEFT JOIN general.country c
    ON c.country_idnum = oi.country_idnum
    LEFT JOIN plant_location pl
    ON oag.plant_location_idnum = pl.plant_location_idnum
    LEFT JOIN purpose pur
    ON pur.purpose_idnum = ob.purpose_idnum
    LEFT JOIN promotion.promotion_to_activity pta
    ON pta.promotion_to_activity_idnum = ob.promotion_to_activity_idnum
    LEFT JOIN promotion.promotion pro
    ON pro.promotion_idnum = pta.promotion_idnum
    LEFT JOIN customer cus_usr
    ON cus_usr.customer_idnum = ob.request_customer_idnum
    LEFT JOIN customer cus
    ON oa.master_customer_idnum = cus.customer_idnum
    LEFT JOIN operation_type ot
    ON ot.operation_type_idnum = cus_usr.operation_type_idnum
    LEFT JOIN ship_priority sp
    ON oag.ship_priority_idnum = sp.ship_priority_idnum
    LEFT JOIN customer_stock_type cst
    ON (cst.master_customer_idnum = ost.master_customer_idnum
    AND cst.stock_type_idnum = ost.product_stock_type_idnum)
    LEFT JOIN accounting.internal_account aia
    ON aia.internal_account_idnum = cst.proteus_internal_account_idnum
    LEFT JOIN `label` lb
    ON lb.label_idnum = ob.label_idnum
    LEFT JOIN customer dist
    ON dist.customer_idnum = oi.distributor_idnum
    WHERE ost.order_stock_type_idnum BETWEEN 162760
    AND 218053
    AND DATE_FORMAT(ob.order_date,'%Y%m%d') = '20070425'
    AND os.status_idnum IN (3,41)
    GROUP BY ost.order_stock_type_idnum
    ORDER BY ost.order_stock_type_idnum
    LIMIT 0,100

    I ran an explain on it and got the following result

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY os range status_idnum,order_activity_idnum status_idnum 4 705961 Using where; Using temporary; Using filesort
    1 PRIMARY oa eq_ref PRIMARY,order_stock_type_idnum,kit_master_idnum PRIMARY 4 shopping.os.order_activity_idnum 1 Using where
    1 PRIMARY ost eq_ref PRIMARY,order_batch_idnum PRIMARY 4 shopping.oa.order_stock_type_idnum 1
    1 PRIMARY ob eq_ref PRIMARY PRIMARY 4 shopping.ost.order_batch_idnum 1 Using where
    1 PRIMARY oi eq_ref PRIMARY PRIMARY 4 shopping.ost.order_idnum 1
    1 PRIMARY pst eq_ref PRIMARY PRIMARY 8 shopping.ost.product_stock_type_idnum 1
    1 PRIMARY oag eq_ref PRIMARY PRIMARY 4 shopping.ost.order_activity_group_idnum 1
    1 PRIMARY sv eq_ref PRIMARY PRIMARY 4 shopping.ost.current_status 1
    1 PRIMARY t ref order_stock_type_idnum order_stock_type_idnum 4 shopping.oa.order_stock_type_idnum 2
    1 PRIMARY p eq_ref PRIMARY PRIMARY 4 shopping.oa.product_idnum 1 Using index
    1 PRIMARY rcon eq_ref PRIMARY PRIMARY 4 shopping.ob.request_contact_idnum 1
    1 PRIMARY mcon eq_ref PRIMARY PRIMARY 4 shopping.ob.master_contact_idnum 1
    1 PRIMARY cd eq_ref PRIMARY PRIMARY 8 shopping.oi.country_division_idnum 1
    1 PRIMARY c eq_ref PRIMARY PRIMARY 8 shopping.oi.country_idnum 1
    1 PRIMARY pl eq_ref PRIMARY PRIMARY 8 shopping.oag.plant_location_idnum 1
    1 PRIMARY pur eq_ref PRIMARY PRIMARY 4 shopping.ob.purpose_idnum 1
    1 PRIMARY pta eq_ref PRIMARY PRIMARY 8 shopping.ob.promotion_to_activity_idnum 1
    1 PRIMARY pro eq_ref PRIMARY PRIMARY 8 promotion.pta.promotion_idnum 1
    1 PRIMARY cus_usr eq_ref PRIMARY PRIMARY 4 shopping.ob.request_customer_idnum 1
    1 PRIMARY cus eq_ref PRIMARY PRIMARY 4 shopping.oa.master_customer_idnum 1
    1 PRIMARY ot eq_ref PRIMARY PRIMARY 8 shopping.cus_usr.operation_type_idnum 1
    1 PRIMARY sp eq_ref PRIMARY PRIMARY 8 shopping.oag.ship_priority_idnum 1
    1 PRIMARY cst ref stock_type_idnum,master_customer_idnum stock_type_idnum 9 shopping.ost.product_stock_type_idnum 4
    1 PRIMARY aia eq_ref PRIMARY PRIMARY 8 shopping.cst.proteus_internal_account_idnum 1 Using index
    1 PRIMARY lb eq_ref PRIMARY PRIMARY 4 shopping.ob.label_idnum 1
    1 PRIMARY dist eq_ref PRIMARY PRIMARY 4 shopping.oi.distributor_idnum 1
    3 DEPENDENT SUBQUERY t_count ref VOID,order_stock_type_idnum order_stock_type_idnum 4 shopping.ost.order_stock_type_idnum 2 Using where
    2 DEPENDENT SUBQUERY oa_count ref order_stock_type_idnum order_stock_type_idnum 5 shopping.oa.order_stock_type_idnum 3 Using where

  • #2
    I just remembered about having functions on the RHS of the equal sign of the where clause.
    I got rid of that and the performance quadrupled

    Comment

    Working...
    X