mysql order by查询慢_mysql orderby 查询过慢优化

SELECT DISTINCT

detail.`id`,

conf1. NAME AS `coal_type_name`,

ord.`main_order_number`,

ord.`coal_name`,

ord.`shipper_name`,

ord.`shipper_phone`,

ord.`shipper_address`,

ord.`shipper_detail_address`,

ord.`consignee_name`,

ord.`consignee_phone`,

ord.`consignee_address`,

ord.`consignee_detail_address`,

detail.`code`,

detail.`order_code`,

(

SELECT

COALESCE (

sum(

CASE

WHEN extra.extra_pay_type = 0 THEN

abs(extra.extra_fee_amount)

ELSE

- abs(extra.extra_fee_amount)

END

),

0

)

FROM

tb_order_extra_fee extra

WHERE

extra.order_code = detail. CODE

AND extra.del_flag = 0

) AS extra_fee,

(

CASE

WHEN detail.`status` = 0 THEN

‘未接单‘

WHEN detail.`status` = 1

AND detail.is_fill = 0 THEN

‘已接单‘

WHEN detail.is_sign = 0

AND detail.is_fill = 1 THEN

‘已装货‘

WHEN detail.`status` = 3 THEN

‘已卸货‘

WHEN detail.`status` = 4 THEN

‘已核算‘

ELSE

‘已打款‘

END

) AS status_name,

detail.`status`,

detail.`create_time`,

detail.`fill_time`,

detail.`receive_time`,

detail.`sign_time`,

detail.`settle_time`,

detail.`update_time`,

detail.`pay_time`,

detail.`delivery_cash_fee`,

detail.`delivery_card_fee`,

detail.`delivery_oil_fee`,

detail.delivery_fee_deserved,

detail.`delivery_fee_practical`,

detail.shipper_delivery_fee,

detail.real_price,

detail.pay_cash_fact,

detail.pay_oil_fact,

detail.pay_steam_fact,

send_driver.`name` AS driver_name,

send_driver.`phone` AS driver_phone,

ur.`name` AS update_name,

ur.`phone` AS update_phone,

send_vehicle.license_number AS vehicle_license,

(

CASE detail_settlement.apply_status

WHEN ‘0‘ THEN

‘未申请‘

WHEN ‘1‘ THEN

‘已申请‘

WHEN ‘2‘ THEN

‘已驳回‘

WHEN ‘4‘ THEN

‘已同意‘

ELSE

‘‘

END

) AS settlement_apply_status,

detail.load_weight AS detail_load_weight,

detail.unload_weight AS detail_unload_weight,

(

SELECT

COALESCE (

sum(

CASE extra.extra_pay_type

WHEN ‘0‘ THEN

extra.extra_fee_amount

ELSE

- extra.extra_fee_amount

END

),

0

)

FROM

tb_order_extra_fee extra

WHERE

extra.order_code = detail. CODE

AND extra.del_flag = 0

) AS extrafee,

send_team.link_man AS team_name,

shipment.user_name AS shipment_name,

shipment.company_name AS shipment_company_name,

(

CASE detail.invoice_status

WHEN ‘0‘ THEN

‘未索取‘

WHEN ‘1‘ THEN

‘已索取‘

WHEN ‘2‘ THEN

‘已开票‘

WHEN ‘7‘ THEN

‘已确认收票‘

ELSE

‘‘

END

) AS invoice_status_name,

(

CASE

WHEN (

detail.`delivery_cash_fee` - detail.`pay_cash_fact` - detail.`pay_oil_fact` - detail.`pay_steam_fact`

) > 0 THEN

(

detail.`delivery_cash_fee` - detail.`pay_cash_fact` - detail.`pay_oil_fact` - detail.`pay_steam_fact`

)

ELSE

‘0‘

END

) AS not_payed_fee

FROM

tb_order_detail detail

INNER JOIN (

SELECT DISTINCT

id

FROM

tb_order_detail detail

WHERE

1 = 1

AND detail.del_flag = 0

AND detail.branch_code = ‘27adb23841d547b8ae6f0c11481b4cfd‘

ORDER BY

detail.id DESC

LIMIT 0,

10

) a USING (id)

LEFT JOIN tb_order ord ON detail.`main_order_code` = ord.`code`

LEFT JOIN tb_shipment shipment ON ord.`create_code` = shipment.`user_code`

LEFT JOIN tb_common_config conf1 ON ord.`coal_type` = conf1.`code`

LEFT JOIN tb_driver send_driver ON detail.`driver_code` = send_driver.`user_code`

LEFT JOIN tb_vehicle send_vehicle ON detail.`vehicle_code` = send_vehicle.`code`

LEFT JOIN tb_driver send_team ON send_team.user_code = detail.create_code

LEFT JOIN tb_user ur ON detail.`update_code` = ur.`code`

AND conf1.`codetype` = ‘coalType‘

LEFT JOIN tb_order_detail_settlement detail_settlement ON detail_settlement.order_detail_code = detail. CODE

原文：https://www.cnblogs.com/leirenyuan/p/12617657.html