My Application is database-driven. I have written a query that is taking more than 15-20 minutes to execute. I have proper indexes, but still, it is taking too much time to execute. Below is my query, if anyone can help me optimize it, I will be very thankful.
select p.id as product_id, p.product_name as product_name, p.product_category as product_category, p.brand as brand, p.sku_id as sku_id, p.product_priority as product_priority, p.quantity as quantity, p.pack as pack, p.unit as unit, p.size as size,
(select group_concat(product_sku) from product_master where product_category=p.product_category and portal='Amazon' and competitor='no') as product_sku_amazon,
(select group_concat(product_sku) from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no') as product_sku_flipkart,
(select group_concat(product_sku) from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no') as product_sku_snapdeal,
(select group_concat(product_sku) from product_master where product_category=p.product_category and portal='1MG' and competitor='no') as product_sku_1mg,
(select group_concat(product_sku) from product_master where product_category=p.product_category and portal='Paytm' and competitor='no') as product_sku_paytm,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_sale_quantity_amazon_date_range,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_sale_value_amazon_date_range,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_selling_price_amazon_date_range,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as sale_quantity_amazon_date_range,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as sale_value_amazon_date_range,
ifnull((select round(sum(sale_value/unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as asp_amazon_date_range,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_sale_quantity_amazon_mtd,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_sale_value_amazon_mtd,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as target_selling_price_amazon_mtd,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as sale_quantity_amazon_mtd,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Amazon' and competitor='no')),0) as sale_value_amazon_mtd,
ifnull((round((select target_sale_quantity_amazon_date_range)/1)),0) as target_sale_quantity_amazon_daily_date_range,
ifnull((round((select target_sale_value_amazon_date_range)/1)),0) as target_sale_value_amazon_daily_date_range,
(select target_selling_price_amazon_date_range) as target_selling_price_amazon_daily_date_range,
ifnull((round((select target_sale_quantity_amazon_mtd)/18)),0) as target_sale_quantity_amazon_daily_mtd,
ifnull((round((select target_sale_value_amazon_mtd)/18)),0) as target_sale_value_amazon_daily_mtd,
(select target_selling_price_amazon_mtd) as target_selling_price_amazon_daily_mtd,
ifnull(round((select target_sale_quantity_amazon_daily_mtd)*30),0) as target_sale_quantity_amazon_mte,
ifnull(round((select target_sale_value_amazon_daily_mtd)*30),0) as target_sale_value_amazon_mte,
ifnull(round(((select sale_quantity_amazon_mtd)/18)*30),0) as sale_quantity_amazon_mte,
ifnull(round(((select sale_value_amazon_mtd)/18)*30),0) as sale_value_amazon_mte,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_sale_quantity_flipkart_date_range,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_sale_value_flipkart_date_range,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_selling_price_flipkart_date_range,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as sale_quantity_flipkart_date_range,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as sale_value_flipkart_date_range,
ifnull((select round(sum(sale_value/unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as asp_flipkart_date_range,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_sale_quantity_flipkart_mtd,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_sale_value_flipkart_mtd,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as target_selling_price_flipkart_mtd,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as sale_quantity_flipkart_mtd,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Flipkart' and competitor='no')),0) as sale_value_flipkart_mtd,
ifnull((round((select target_sale_quantity_flipkart_date_range)/1)),0) as target_sale_quantity_flipkart_daily_date_range,
ifnull((round((select target_sale_value_flipkart_date_range)/1)),0) as target_sale_value_flipkart_daily_date_range,
(select target_selling_price_flipkart_date_range) as target_selling_price_flipkart_daily_date_range,
ifnull((round((select target_sale_quantity_flipkart_mtd)/18)),0) as target_sale_quantity_flipkart_daily_mtd,
ifnull((round((select target_sale_value_flipkart_mtd)/18)),0) as target_sale_value_flipkart_daily_mtd,
(select target_selling_price_flipkart_mtd) as target_selling_price_flipkart_daily_mtd,
ifnull(round((select target_sale_quantity_flipkart_daily_mtd)*30),0) as target_sale_quantity_flipkart_mte,
ifnull(round((select target_sale_value_flipkart_daily_mtd)*30),0) as target_sale_value_flipkart_mte,
ifnull(round(((select sale_quantity_flipkart_mtd)/18)*30),0) as sale_quantity_flipkart_mte,
ifnull(round(((select sale_value_flipkart_mtd)/18)*30),0) as sale_value_flipkart_mte,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_sale_quantity_snapdeal_date_range,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_sale_value_snapdeal_date_range,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_selling_price_snapdeal_date_range,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as sale_quantity_snapdeal_date_range,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as sale_value_snapdeal_date_range,
ifnull((select round(sum(sale_value/unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as asp_snapdeal_date_range,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_sale_quantity_snapdeal_mtd,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_sale_value_snapdeal_mtd,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as target_selling_price_snapdeal_mtd,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as sale_quantity_snapdeal_mtd,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Snapdeal' and competitor='no')),0) as sale_value_snapdeal_mtd,
ifnull((round((select target_sale_quantity_snapdeal_date_range)/1)),0) as target_sale_quantity_snapdeal_daily_date_range,
ifnull((round((select target_sale_value_snapdeal_date_range)/1)),0) as target_sale_value_snapdeal_daily_date_range,
(select target_selling_price_snapdeal_date_range) as target_selling_price_snapdeal_daily_date_range,
ifnull((round((select target_sale_quantity_snapdeal_mtd)/18)),0) as target_sale_quantity_snapdeal_daily_mtd,
ifnull((round((select target_sale_value_snapdeal_mtd)/18)),0) as target_sale_value_snapdeal_daily_mtd,
(select target_selling_price_snapdeal_mtd) as target_selling_price_snapdeal_daily_mtd,
ifnull(round((select target_sale_quantity_snapdeal_daily_mtd)*30),0) as target_sale_quantity_snapdeal_mte,
ifnull(round((select target_sale_value_snapdeal_daily_mtd)*30),0) as target_sale_value_snapdeal_mte,
ifnull(round(((select sale_quantity_snapdeal_mtd)/18)*30),0) as sale_quantity_snapdeal_mte,
ifnull(round(((select sale_value_snapdeal_mtd)/18)*30),0) as sale_value_snapdeal_mte,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_sale_quantity_1mg_date_range,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_sale_value_1mg_date_range,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_selling_price_1mg_date_range,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as sale_quantity_1mg_date_range,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as sale_value_1mg_date_range,
ifnull((select round(sum(sale_value/unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as asp_1mg_date_range,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_sale_quantity_1mg_mtd,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_sale_value_1mg_mtd,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as target_selling_price_1mg_mtd,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as sale_quantity_1mg_mtd,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='1MG' and competitor='no')),0) as sale_value_1mg_mtd,
ifnull((round((select target_sale_quantity_1mg_date_range)/1)),0) as target_sale_quantity_1mg_daily_date_range,
ifnull((round((select target_sale_value_1mg_date_range)/1)),0) as target_sale_value_1mg_daily_date_range,
(select target_selling_price_1mg_date_range) as target_selling_price_1mg_daily_date_range,
ifnull((round((select target_sale_quantity_1mg_mtd)/18)),0) as target_sale_quantity_1mg_daily_mtd,
ifnull((round((select target_sale_value_1mg_mtd)/18)),0) as target_sale_value_1mg_daily_mtd,
(select target_selling_price_1mg_mtd) as target_selling_price_1mg_daily_mtd,
ifnull(round((select target_sale_quantity_1mg_daily_mtd)*30),0) as target_sale_quantity_1mg_mte,
ifnull(round((select target_sale_value_1mg_daily_mtd)*30),0) as target_sale_value_1mg_mte,
ifnull(round(((select sale_quantity_1mg_mtd)/18)*30),0) as sale_quantity_1mg_mte,
ifnull(round(((select sale_value_1mg_mtd)/18)*30),0) as sale_value_1mg_mte,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_sale_quantity_paytm_date_range,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_sale_value_paytm_date_range,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_selling_price_paytm_date_range,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as sale_quantity_paytm_date_range,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as sale_value_paytm_date_range,
ifnull((select round(sum(sale_value/unit_sold)) from sale_data where (date between '2021-09-18' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as asp_paytm_date_range,
ifnull((select round(sum(target_sale_quantity)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_sale_quantity_paytm_mtd,
ifnull((select round(sum(target_sale_quantity*target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_sale_value_paytm_mtd,
ifnull((select round(avg(target_selling_price)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as target_selling_price_paytm_mtd,
ifnull((select round(sum(unit_sold)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as sale_quantity_paytm_mtd,
ifnull((select round(sum(sale_value)) from sale_data where (date between '2021-09-01' and '2021-09-18') and product_id in (select id from product_master where product_category=p.product_category and portal='Paytm' and competitor='no')),0) as sale_value_paytm_mtd,
ifnull((round((select target_sale_quantity_paytm_date_range)/1)),0) as target_sale_quantity_paytm_daily_date_range,
ifnull((round((select target_sale_value_paytm_date_range)/1)),0) as target_sale_value_paytm_daily_date_range,
(select target_selling_price_paytm_date_range) as target_selling_price_paytm_daily_date_range,
ifnull((round((select target_sale_quantity_paytm_mtd)/18)),0) as target_sale_quantity_paytm_daily_mtd,
ifnull((round((select target_sale_value_paytm_mtd)/18)),0) as target_sale_value_paytm_daily_mtd,
(select target_selling_price_paytm_mtd) as target_selling_price_paytm_daily_mtd,
ifnull(round((select target_sale_quantity_paytm_daily_mtd)*30),0) as target_sale_quantity_paytm_mte,
ifnull(round((select target_sale_value_paytm_daily_mtd)*30),0) as target_sale_value_paytm_mte,
ifnull(round(((select sale_quantity_paytm_mtd)/18)*30),0) as sale_quantity_paytm_mte,
ifnull(round(((select sale_value_paytm_mtd)/18)*30),0) as sale_value_paytm_mte,
((select target_sale_quantity_amazon_date_range)+(select target_sale_quantity_flipkart_date_range)+(select target_sale_quantity_snapdeal_date_range)+(select target_sale_quantity_1mg_date_range)+(select target_sale_quantity_paytm_date_range)) as target_sale_quantity_total_date_range,
((select target_sale_value_amazon_date_range)+(select target_sale_value_flipkart_date_range)+(select target_sale_value_snapdeal_date_range)+(select target_sale_value_1mg_date_range)+(select target_sale_value_paytm_date_range)) as target_sale_value_total_date_range,
ifnull(round(((select target_selling_price_amazon_date_range)+(select target_selling_price_flipkart_date_range)+(select target_selling_price_snapdeal_date_range)+(select target_selling_price_1mg_date_range)+(select target_selling_price_paytm_date_range))/5),0) as target_selling_price_total_date_range,
((select sale_quantity_amazon_date_range)+(select sale_quantity_flipkart_date_range)+(select sale_quantity_snapdeal_date_range)+(select sale_quantity_1mg_date_range)+(select sale_quantity_paytm_date_range)) as sale_quantity_total_date_range,
((select sale_value_amazon_date_range)+(select sale_value_flipkart_date_range)+(select sale_value_snapdeal_date_range)+(select sale_value_1mg_date_range)+(select sale_value_paytm_date_range)) as sale_value_total_date_range,
ifnull(round(((select asp_amazon_date_range)+(select asp_flipkart_date_range)+(select asp_snapdeal_date_range)+(select asp_1mg_date_range)+(select asp_paytm_date_range))/5),0) as asp_total_date_range,
((select target_sale_quantity_amazon_mtd)+(select target_sale_quantity_flipkart_mtd)+(select target_sale_quantity_snapdeal_mtd)+(select target_sale_quantity_1mg_mtd)+(select target_sale_quantity_paytm_mtd)) as target_sale_quantity_total_mtd,
((select target_sale_value_amazon_mtd)+(select target_sale_value_flipkart_mtd)+(select target_sale_value_snapdeal_mtd)+(select target_sale_value_1mg_mtd)+(select target_sale_value_paytm_mtd)) as target_sale_value_total_mtd,
ifnull(round(((select target_selling_price_amazon_mtd)+(select target_selling_price_flipkart_mtd)+(select target_selling_price_snapdeal_mtd)+(select target_selling_price_1mg_mtd)+(select target_selling_price_paytm_mtd))/5),0) as target_selling_price_total_mtd,
((select sale_quantity_amazon_mtd)+(select sale_quantity_flipkart_mtd)+(select sale_quantity_snapdeal_mtd)+(select sale_quantity_1mg_mtd)+(select sale_quantity_paytm_mtd)) as sale_quantity_total_mtd,
((select sale_value_amazon_mtd)+(select sale_value_flipkart_mtd)+(select sale_value_snapdeal_mtd)+(select sale_value_1mg_mtd)+(select sale_value_paytm_mtd)) as sale_value_total_mtd,
((select target_sale_quantity_amazon_daily_date_range)+(select target_sale_quantity_flipkart_daily_date_range)+(select target_sale_quantity_snapdeal_daily_date_range)+(select target_sale_quantity_1mg_daily_date_range)+(select target_sale_quantity_paytm_daily_date_range)) as target_sale_quantity_total_daily_date_range,
((select target_sale_value_amazon_daily_date_range)+(select target_sale_value_flipkart_daily_date_range)+(select target_sale_value_snapdeal_daily_date_range)+(select target_sale_value_1mg_daily_date_range)+(select target_sale_value_paytm_daily_date_range)) as target_sale_value_total_daily_date_range,
ifnull(round(((select target_selling_price_amazon_daily_date_range)+(select target_selling_price_flipkart_daily_date_range)+(select target_selling_price_snapdeal_daily_date_range)+(select target_selling_price_1mg_daily_date_range)+(select target_selling_price_paytm_daily_date_range))/5),0) as target_selling_price_total_daily_date_range,
((select target_sale_quantity_amazon_daily_mtd)+(select target_sale_quantity_flipkart_daily_mtd)+(select target_sale_quantity_snapdeal_daily_mtd)+(select target_sale_quantity_1mg_daily_mtd)+(select target_sale_quantity_paytm_daily_mtd)) as target_sale_quantity_total_daily_mtd,
((select target_sale_value_amazon_daily_mtd)+(select target_sale_value_flipkart_daily_mtd)+(select target_sale_value_snapdeal_daily_mtd)+(select target_sale_value_1mg_daily_mtd)+(select target_sale_value_paytm_daily_mtd)) as target_sale_value_total_daily_mtd,
ifnull(round(((select target_selling_price_amazon_daily_mtd)+(select target_selling_price_flipkart_daily_mtd)+(select target_selling_price_snapdeal_daily_mtd)+(select target_selling_price_1mg_daily_mtd)+(select target_selling_price_paytm_daily_mtd))/5),0) as target_selling_price_total_daily_mtd,
((select target_sale_quantity_amazon_mte)+(select target_sale_quantity_flipkart_mte)+(select target_sale_quantity_snapdeal_mte)+(select target_sale_quantity_1mg_mte)+(select target_sale_quantity_paytm_mte)) as sale_target_quantity_total_mte,
((select target_sale_value_amazon_mte)+(select target_sale_value_flipkart_mte)+(select target_sale_value_snapdeal_mte)+(select target_sale_value_1mg_mte)+(select target_sale_value_paytm_mte)) as target_sale_value_total_mte,
ifnull(round(((select target_selling_price_amazon_mtd)+(select target_selling_price_flipkart_mtd)+(select target_selling_price_snapdeal_mtd)+(select target_selling_price_1mg_mtd)+(select target_selling_price_paytm_mtd))/5),0) as target_selling_price_total_mte,
((select sale_quantity_amazon_mte)+(select sale_quantity_flipkart_mte)+(select sale_quantity_snapdeal_mte)+(select sale_quantity_1mg_mte)+(select sale_quantity_paytm_mte)) as sale_quantity_total_mte,
((select sale_value_amazon_mte)+(select sale_value_flipkart_mte)+(select sale_value_snapdeal_mte)+(select sale_value_1mg_mte)+(select sale_value_paytm_mte)) as sale_value_total_mte
from product_master p
where p.competitor='no' and p.parent_product_category in ('Mosquito Net')
group by p.product_category
order by sale_quantity_total_date_range desc
The above is a huge query and I have only this option to achieve desired result.I also have MySQL caching turned on, but it doesn't help.
Your query is too large for a volunteer to take on, to put it mildly. But I will point this out: It has dozens of dependent subqueries. Those are a notorious performance antipattern.
I know the first six dependent subqueries are completely unnecessary if you use conditional aggregation. Here's how I would rewrite this: it will probably be several times faster than what you have.
This works because
GROUP_CONCAT()ignores NULL values.I suspect you can rewrite much of your query this way.