optimization for select of select in mysql query

Question

My mysql query is taking nearly 7 seconds to fetch data, where I used select of select.

SELECT s.id
     , s.user_id
     , COUNT(s.od_status) od_status
     , (SELECT count(t2.od_status) 
          FROM subscription t2 
         WHERE od_status <> 3 
           AND t2.od_status <> 7 
           AND t2.od_status <> 8 
           AND t2.user_id = s.user_id 
           AND DATE(IF(t2.rescheduling_delivery_date IS NULL, t2.dated, t2.rescheduling_delivery_date)) BETWEEN DATE_FORMAT('2021-07-26', '%Y-%m-01') AND '2021-07-26'
        ) od_status_count 
  FROM subscription 
  LEFT 
  JOIN users u
    ON u.id = s.user_id 
 WHERE DATE(IF(s.rescheduling_delivery_date IS NULL, s.dated, s.rescheduling_delivery_date)) BETWEEN DATE_FORMAT('2021-07-26' , '%Y-%m-01') AND '2021-07-26' 
 GROUP 
    BY s.user_id;

I need to optimize the above query, the thing I am getting 7 secs is by adding below select of select in above query

(select count(t2.od_status) from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and t2.user_id=subscription.user_id and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26') as od_status_count

any suggestion to optimize or another way to use that.

NOTE: I have given index to the user_id in subscription table

Answer 1

As @RahulBiswas suggested, I changed the subquery to LEFT JOIN and the query executed in 0.16 secs

select 
`subscription`.`id`, 
`subscription`.`user_id`, 
COUNT(subscription.od_status) as od_status ,
x.od_status_count
from `subscription` left join `users` on `users`.`id` = `subscription`.`user_id`
left join(
  select t2.user_id,count(t2.od_status)as od_status_count from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26'
group by t2.user_id)x
on x.user_id=subscription.user_id    
where date(IF(subscription.rescheduling_delivery_date IS NULL,subscription.dated,subscription.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26' 
group by `subscription`.`user_id`

I changed the subquery to joins as below,

left join(
select t2.user_id,count(t2.od_status)as od_status_count from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26'
group by t2.user_id)x
on x.user_id=subscription.user_id