sql - How do I optimize this mysql query with nested joins? -

below query need optimize.

select         upper(ifnull(op.supplier_payment_method,s.default_payment_method)) payment_method,         op.supplier_payment_date payment_date,         date_format(ifnull(op.supplier_payment_date,op.ship_date),'%m %y') payment_month,         s.supplier_name farm,         op.sub_order_id order_num,         date_format(op.ship_date,'%b-%d-%y') ship_date,         op.farm_credit farm_credit,         op.credit_memo credit_memo,         op.credit_description credit_description,         opb.boxes box_type,         concat('$',format(op.box_charge,2)) box_charge,         op.invoice_num invoice_num,         concat('$',format(op.invoice_amt,2)) invoice_amt,         concat('$',format(op.total_invoice_amt,2)) total_invoice_amt,         concat(op.um_qty,' ',op.um_type) st_bu_qty,         op.po_product_name invoice_desc,         concat('$',format((op.price_um*op.um_qty),2)) cost_product_cms,         op.supplier_invoice_note supplier_invoice_notes,         concat('$',format(op.cms_invoice_cost,2)) cms_invoice_diff,         concat('$',format(op.total_farm_cost,2)) farm_cost              orders_products op         inner join              suppliers s on s.supplier_id = op.supplier_name         left join             (                 select                      sub_order_id,                      group_concat(concat(box_type_qty,' ',bo.box_option_name) separator ', ') boxes                                     order_products_boxes opb                     inner join box_options bo on bo.id=opb.box_type_id                 group                     opb.sub_order_id             ) opb on opb.sub_order_id = op.sub_order_id             op.order_active=0         , op.ship_date>='2013-03-01'         , op.ship_date<='2013-04_01'     order op.ship_date desc 

as can see, query comprises of 4 tables, each containing around 20k-30k rows each. add in sub-query, query becomes exceptionally slow. takes approx 1.5mins fetch 500 rows of record. there way speed things within single query?

your left-join query pre-concatenates sub-box options querying entire database first, joins within criteria limiting outer clause. may little more effort, pull same outer criteria inner query should help. doing field-level sql-select on per-every-row basis performance killer.

you should need change @ "from" clause...

from    orders_products op       inner join suppliers s           on op.supplier_name = s.supplier_id        left join          ( select                   opb2.sub_order_id,                   group_concat(concat(box_type_qty,' ',bo.box_option_name) separator ', ') boxes                                orders_products op2                     join order_products_boxes opb2                        on op2.sub_order_id = opb2.sub_order_id                        inner join box_options bo                            on opb2.box_type_id = bo.id                                    op2.order_active = 0                  , op2.ship_date >= '2013-03-01'                  , op2.ship_date <= '2013-04_01'               group                  opb2.sub_order_id  ) opb           on op.sub_order_id = opb.sub_order_id        op.order_active = 0    , op.ship_date >= '2013-03-01'    , op.ship_date <= '2013-04_01'