MySQL Query statement optimization

Use explain to optimize the query of a Business day summary report. Change a query of type all to range.

SQL is a report using:

EXPLAINSELECTDate_format (T1.business_date,'%y-%m-%d') asBusiness_date,IF(T3.people_num>0, (T1.need_pay_amount/T3.people_num),0) asAvg_people_money,.. from  (SELECT(SUM(Timer_charge)+ SUM(Time_out_money)) asTimer_charge,0.00  asAvg_people_money,.. fromconsumption_history CWHEREC. ' Status_code '= 1  andC. ' Business_date '>='2014-12-04'  andC. ' Business_date '<='2014-12-04'GROUP  byc. ' business_date ') T1 Left JOIN (SELECTGroup_concat (CONCAT (P.payment_pattern_name,'=', H.payment_money)) asPayment_money_info,SUM(IF(P.payment_property=1, H.payment_money,0)) asActually_pay_money,.. fromconsumption_history CINNER JOINConsumption_payment_history h onh.consumption_id=c. ' consumption_id 'INNER JOINPayment_pattern P onH. ' Payment_type_id '=p. ' payment_pattern_id 'WHEREC. ' Status_code '= 1   andC. ' Business_date '>='2014-12-04'  andC. ' Business_date '<='2014-12-04'GROUP  byc. ' business_date ') T2 onT1.business_date=t2.business_date Left JOIN (SELECTBusiness_date,SUM(People_num) asPeople_num,SUM(Order_amount) asDish_sale_amount fromdining_order_history CWHEREC. ' Status_code '= 1   andC. ' Business_date '>='2014-12-04'  andC. ' Business_date '<='2014-12-04'GROUP  byc. ' business_date ') T3 onT1.business_date=t3.business_dateORDER  byT1. ' Business_date '

You can see the results of the explain, which highlights the next few columns

Make a simple description of the type query method

In the case of a consumption_history up to tens of thousands of (the amount of data forget), performance optimization is quite obvious, from 30 seconds of connection timeout, to 1 seconds out of the results.

It seems that the above SQL is optimized for comparison in place.

MySQL Query statement optimization