php - 我想优化这个查询，用子查询创建新表 select 没问题，但问题是查询运行需要 30 分钟

Question

我想优化这个查询。查询花费的时间太长(30 分钟)

Create Table newtable
Select start_date, CAST(odo_meter AS UNSIGNED) current_odo, 
CAST(odo_meter AS UNSIGNED) - ifnull((Select odo_meter from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id order by custtrans.start_date asc limit 1),CAST(odo_meter AS UNSIGNED)) KmTraveled,
custmain.total_cost current_cost,
custmain.litres current_litre,
(Select sum(total_cost) from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id) `total_cost`,
(Select sum(litres) from customer_transactions_dashboard custtrans where custmain.start_date > custtrans.start_date
and CAST(odo_meter AS UNSIGNED) > 0 and custmain.site_id = custtrans.site_id and
custmain.vehicle_id = custtrans.vehicle_id) `total_liter`,
custmain.site_id, custmain.vehicle_id
from customer_transactions_dashboard custmain;

Answer 1

您可以对站点和车辆使用分组依据的联接，而不是对每行和列重复选择

select  c.start_date
  , CAST(c.odo_meter AS UNSIGNED) current_odo - ifnull(t.odo_meter, c.odo_meter
  , c.total_cost current_cost
  , c.litres current_litre
  , t.total_cost
  , t.total_liter
from  customer_transactions_dashboard c
INNER JOIN  (
    select  site_id
        , vehicle_id
        , odo_meter
        , sum(total_cost) `total_cost`
        , sum(litres) `total_liter`
    from customer_transactions_dashboard
    where custmain.start_date > custtrans.start_date
    and CAST(odo_meter AS UNSIGNED) > 0 
    group by  site_id, vehicle_id
) t on t.site_id = c.site_id 
    and t.vehicle_id = c.vehicle_id

确保表 customer_transactions_dashboard 列 site_id、vehicle_id 上有正确的索引以及同一个表上的 start_date 列的第二个索引