有沒有大神可以提出更好的優化建議，現在查詢時間大概在10秒左右。索引都已經加入了
(
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS sum_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
FROM
`billing_details`
INNER JOIN `payments_total` ON payments_total.payment_id = billing_details.payment_id
INNER JOIN `member` ON member.shop_Id = billing_details.store_id
WHERE
payments_total.`status` = 'succ'
AND billing_details.`status` = 1
AND payments_total.disabled = 'false'
AND billing_details.disabled = 'false'
AND payments_total.trade_code IN (
'3021',
'3030',
'3031',
'3026',
'3027',
'3028'
)
GROUP BY
`payments_total`.`store_id`
ORDER BY
`billing_details`.`t_time` DESC
)
UNION
(
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS su (
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS sum_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
FROM
`billing_details`
INNER JOIN `payments_total` ON payments_total.payment_id = billing_details.payment_id
INNER JOIN `member` ON member.shop_Id = billing_details.store_id
WHERE
payments_total.`status` = 'succ'
AND billing_details.`status` = 1
AND payments_total.disabled = 'false'
AND billing_details.disabled = 'false'
AND payments_total.trade_code IN (
'3021',
'3030',
'3031',
'3026',
'3027',
'3028'
)
GROUP BY
`payments_total`.`store_id`
ORDER BY
`billing_details`.`t_time` DESC
)
UNION
(
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS sum_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
FROM
`billing_details`
INNER JOIN `payments_total` ON payments_total.payment_id = billing_details.payment_id
INNER JOIN `member` ON member.shop_Id = billing_details.store_id
WHERE
payments_total.`status` = 'succ'
AND billing_details.`status` = 1
AND payments_total.disabled = 'false'
AND billing_details.disabled = 'false'
AND payments_total.trade_code IN (
'3041',
'3042',
'3043',
'3044',
'3045',
'3046'
)
GROUP BY
`payments_total`.`store_id`
ORDER BY
`billing_details`.`t_time` DESC
) m_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
FROM
`billing_details`
INNER JOIN `payments_total` ON payments_total.payment_id = billing_details.payment_id
INNER JOIN `member` ON member.shop_Id = billing_details.store_id
WHERE
payments_total.`status` = 'succ'
AND billing_details.`status` = 1
AND payments_total.disabled = 'false'
AND billing_details.disabled = 'false'
AND payments_total.trade_code IN (
'3041',
'3042',
'3043',
'3044',
'3045',
'3046'
)
GROUP BY
`payments_total`.`store_id`
ORDER BY
`billing_details`.`t_time` DESC
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/108016.html
標籤：MySQL