mysql views query very slow

Question

I have created a view in 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');

It has thousands of records and performs very slow. Any query i run takes about 50 seconds. Can you please help me find the mistake or recommend a better way to create the view.

Thanks in advance

Answer 1

First, I would indent your query so that I can understand its structure. I did that and I have the following image now

And then I noticed that you were using a correlated sub-query in Lines 36-53 (see Wikipedia), which can be inefficient. So I would try rewriting the query to use a JOIN instead. The new query would look like this

Finally, I would review if the two tables have been properly indexed on the column invoice.id and invoice_items.fk_invoice.

Here is the text of the code of "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             
;