MySQL的意见查询很慢

Question

我已经在MySQL

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `invoice_cad_view` AS select `a`.`operator` AS `operator`,`a`.`flag_needs_reporting` AS `flag_needs_reporting`,`a`.`reported_time` AS `reported_time`,`a`.`reporting_type` AS `reporting_type`,`a`.`fk_company` AS `fk_company`,`a`.`fk_branch` AS `fk_branch`,(((case when (`a`.`payment_type_1_m` = 'cash') then `a`.`payment_type_1` else 0 end) + (case when (`a`.`payment_type_2_m` = 'cash') then `a`.`payment_type_2` else 0 end)) + (case when (`a`.`payment_type_3_m` = 'cash') then `a`.`payment_type_3` else 0 end)) AS `cash`,(((case when (`a`.`payment_type_1_m` = 'debit') then `a`.`payment_type_1` else 0 end) + (case when (`a`.`payment_type_2_m` = 'debit') then `a`.`payment_type_2` else 0 end)) + (case when (`a`.`payment_type_3_m` = 'debit') then `a`.`payment_type_3` else 0 end)) AS `debit`,(((case when (`a`.`payment_type_1_m` = 'credit') then `a`.`payment_type_1` else 0 end) + (case when (`a`.`payment_type_2_m` = 'credit') then `a`.`payment_type_2` else 0 end)) + (case when (`a`.`payment_type_3_m` = 'credit') then `a`.`payment_type_3` else 0 end)) AS `credit`,(((case when (`a`.`payment_type_1_m` = 'other') then `a`.`payment_type_1` else 0 end) + (case when (`a`.`payment_type_2_m` = 'other') then `a`.`payment_type_2` else 0 end)) + (case when (`a`.`payment_type_3_m` = 'other') then `a`.`payment_type_3` else 0 end)) AS `other`,((`a`.`payment_type_1` + `a`.`payment_type_2`) + `a`.`payment_type_3`) AS `total_value`,`a`.`last_updated` AS `last_updated`,`a`.`created_date` AS `created_date`,(((select sum((case when (`invoice_items`.`type` = 'sell') then (`invoice_items`.`unit_price` * `invoice_items`.`quantity`) else (-(`invoice_items`.`unit_price`) * `invoice_items`.`quantity`) end)) from `invoice_items` where ((`invoice_items`.`fk_invoice` = `a`.`id`) and (`invoice_items`.`flag_is_deleted` = 'no'))) + `a`.`service_fee`) + `a`.`service_tax`) AS `subtotal` from `invoice` `a` where (`a`.`flag_is_deleted` = 'no'); 

它有上千条记录，并执行很慢创建的视图。我运行的任何查询需要大约50秒。你能帮我找到这个错误，或者建议一个更好的方法来创建视图。

在此先感谢

Answer 1

首先，我会缩进您的查询，以便我可以了解其结构。我这样做，我有下面的图片现在

然后我注意到，你在线路36-53 使用相关子查询 （见Wikipedia），这可能是低效的。所以我会尝试 重写查询以使用JOIN。新的查询看起来 这样

最后，我会检讨，如果这两个表已经适当在列invoice.id和invoice_items.fk_invoice索引 。

这里是“after.sql”

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `invoice_cad_view` AS 
select 
    `a`.`operator` AS `operator` 
    ,`a`.`flag_needs_reporting` AS `flag_needs_reporting` 
    ,`a`.`reported_time` AS `reported_time` 
    ,`a`.`reporting_type` AS `reporting_type` 
    ,`a`.`fk_company` AS `fk_company` 
    ,`a`.`fk_branch` AS `fk_branch` 
    ,(((case when 
     (`a`.`payment_type_1_m` = 'cash') then `a`.`payment_type_1` else 0 end) 
     + 
     (case when (`a`.`payment_type_2_m` = 'cash') then `a`.`payment_type_2` else 0 end) 
    ) + 
    (case when (`a`.`payment_type_3_m` = 'cash') then `a`.`payment_type_3` else 0 end) 
    ) AS `cash` 
    ,(((case when (`a`.`payment_type_1_m` = 'debit') then `a`.`payment_type_1` else 0 end) 
     + (case when (`a`.`payment_type_2_m` = 'debit') then `a`.`payment_type_2` else 0 end) 
    ) + 
    (case when (`a`.`payment_type_3_m` = 'debit') then `a`.`payment_type_3` else 0 end)) AS `debit` 
    ,(((case when (`a`.`payment_type_1_m` = 'credit') then `a`.`payment_type_1` else 0 end) 
     + 
    (case when (`a`.`payment_type_2_m` = 'credit') then `a`.`payment_type_2` else 0 end) 
    ) 
    + (case when (`a`.`payment_type_3_m` = 'credit') then `a`.`payment_type_3` else 0 end) 
    ) AS `credit` 
    ,(((case when (`a`.`payment_type_1_m` = 'other') then `a`.`payment_type_1` else 0 end) 
     + 
    (case when (`a`.`payment_type_2_m` = 'other') then `a`.`payment_type_2` else 0 end) 
    ) 
    + (case when (`a`.`payment_type_3_m` = 'other') then `a`.`payment_type_3` else 0 end) 
    ) AS `other` 
    ,((`a`.`payment_type_1` + `a`.`payment_type_2`) + `a`.`payment_type_3`) AS `total_value` 
    ,`a`.`last_updated` AS `last_updated` 
    ,`a`.`created_date` AS `created_date` 
    ,(((
      sum((case when 
        (`invoice_items`.`type` = 'sell') then 
         (`invoice_items`.`unit_price` * `invoice_items`.`quantity`) 
        else 
         (-(`invoice_items`.`unit_price`) * `invoice_items`.`quantity`) 
       end) 
      ) 
     ) 
     + `a`.`service_fee` 
    ) 
    + `a`.`service_tax` 
    ) AS `subtotal` 
from 
    `invoice` `a` 
    , `invoice_items` 
where 
    (`a`.`flag_is_deleted` = 'no') 
    and 
    (`invoice_items`.`fk_invoice` = `a`.`id`) 
    and (`invoice_items`.`flag_is_deleted` = 'no') 
    ) 
group by `a`.id    
;